Duration To Hourly Buckets

3214100

Hello Everyone,

We have the below requirement of bucketing the duration into hourly buckets.

   

SHOP_CODE	PUNCH_IN_TIME	PUNCH_OUT_TIME
1111	4/17/2017 12:00	4/17/2017 12:00
1111	4/25/2017 12:00	4/25/2017 12:00
1112	11/19/2016 9:00	11/19/2016 13:25
1112	11/19/2016 14:10	11/19/2016 17:40
1113	4/10/2017 9:00	4/10/2017 14:00
1113	4/17/2017 10:00	4/17/2017 12:00

Output should look like :

        

Shop	Business Date	9 to 10	10 to 11	11 to 12	12 to 1	1 to 2	2 to 3	3 to 4	4 to 5	5 to 6	6 to 7	7 to 8	8 to 9	9 to 10
1111	4/17/2017	0	0	0	0	0	0	0	0	0	0	0	0	0
1111	4/25/2017	0	0	0	0	0	0	0	0	0	0	0	0	0
1112	11/19/2016	1	1	1	1	0.41	0.83	1	1	0.66	0	0	0	0

Any suggestion are appreciated.

Thanks !!

Thomas Dodds

My suggestion:

1. Date dimension

2. Hour Bucket dimension

3. Fact table grain:  shop, date, hour bucket

     Fact measure: duration in hours

^ then model in RPD

John_K

Agree totally - the most flexible (and correct) way to do this would be model as a separate dimension. However if you absolutely cannot do that (for whatever reason) then you can build a logical column on this and simply subtract the two dates, convert to hours and "bucket" with a case statement. If you don't have RPD access then you can of course do this in the presentation layer (which has bucketing mechanisms anyway) and save as a saved column (if your version of OBIEE supports that).