Dynamic time intervals

[sbfernandes]

Greetings.

I am trying to create intervals to cover the entire day (00:00 - 23:59) based on the input table. My input table is as follows: 

Category	Time	Value
A	10:15	15
A	16:29	25
A	18:00	30
B	12:00	29

Required output table

Category	Time Start	Time End	Avg Value Calculation
A	00:00	09:59	AVG(15,25,30)
A	10:00	10:29	AVG(15)
A	10:30	15:59	AVG(15,25,30)
A	16:00	16:29	AVG(25)
A	16:30	17:59	AVG(15,25,30)
A	18:00	18:29	AVG(30)
A	18:30	23:59	AVG(15,25,30)
B	00:00	11:59	AVG(29)
B	12:00	12:29	AVG(29)
B	12:30	23:59	AVG(29)

Appreciate any assistance.

[jonathandienst]

Is not clear how you get from your data to the required output.  For example the first three rows look inconsistent with interval mapping the input. And how are the intervals determined?

[sbfernandes]

Hi Jontydkpi

Category A has values at 3 times 10:15, 16:29, 18:00.

I would like to create time bands for Category A to cover the entire day up to half hour interval.

For Time 10:15,

time band is 1000 - 1029.,

The previous time band will be 0000 - 0959

Next time band will be 1030 - 1559.

For time 16:29, time band is 1600 - 1629.

next time band is 1630 - 1759.

For 18:00, time band is 1800 - 1829.

As last value is 1800, the next time band will go upto end of day i.e. 1830 - 2359.

Hope this makes sense

[jonathandienst]

So you mean create a time band of 30 minutes for each time in your source?

[jonathandienst]

Now please explain the average calc for A.

[sbfernandes]

Yes.

For Time that exists, the time band will be 30 mins. I also need time bands that will cover the entire 24 hours. So for Category A, the immediate time bands will be 1000 - 1029 (10:15), 1600 - 1629 (16:29) and 1800 - 1829 (18:00). In addition, I need to create time bands 0000 - 0959, 1030 - 1559, 1630 - 1759 and 1830 - 2359. All these 7 time bands cover the entire day.

The average calculation is not an issue. When the Time is present then the average will be value in that time band. Eg: for 1000 - 1029, it will be based on value 15. For time bands that do not exist, it will be based on Average of the Category (in this case A) i.e. AVG(15,25,30).

Primarily i cant figure out how to get the time bands to cover 24 hours.