Creating quarter hour table

[Quy_Nguyen]

Hi everyone,

I am creating a quarter hour table by this script:

TimeDimension:
  Load
  Num(rowno()-1, '00') as TimeKey,
  Time(Floor((rowno()-1)/96, 1/24), 'hh:mm') as Hour,
  Time(Floor((rowno()-1)/96, 1/48), 'hh:mm') as HalfHour,
  Time((rowno()-1)/96, 'hh:mm') as QuarterHour
AutoGenerate 96;

It looks okay to me. But the result is a little bit weird as this:

For each hour, I will have 4 quarter hour. But with the TimeKey 28, I got the quarter hour is 7:00, its hour and half hour are not correct.

If I change the code by adding a magic number like this, I got the correct result:

TimeDimension:
Load
  Num(rowno()-1, '00') as TimeKey,
  Time(Floor((rowno()-0.9)/96, 1/24), 'hh:mm') as Hour,
  Time(Floor((rowno()-0.9)/96, 1/48), 'hh:mm') as HalfHour,
  Time((rowno()-1)/96, 'hh:mm') as QuarterHour
AutoGenerate 96;

Can anyone help me to explain what's wrong with the first script?

[sunny_talwar · Accepted solution]

This has to do with Rounding Errors

[Quy_Nguyen]

Thank you Sunny!