Split Start Date and End Date into Hours Per Days

[mgDev]

Hello,

@MK_QSL << Hi Manish adding you as I have seen you have answered a similar before. Hoping you can help this time too! 😄

I have a table which has the following structure;

Work Center Name	Start Date	Stop Date
AA	2020-06-09 19:34:11.34113411	2020-06-10 09:29:45.29452945

And for each entry I would like to get this,

Work Center Name	Date	Duration (Hours)
AA	09/06/2020	3.5
AA	10/06/2020	9.5

I have tried various approaches - this is my latest;

Load

Name as [Facility],
timestamp(StartDate) as [StartDate],
timestamp(StopDate) as [StopDate];

NoConcatenate

[Downtime]:
Load
*,

Interval(StopDate-StartDate,'HH:mm:ss') as DurationHours,

Date(Floor(StartDate)) as [Date];

Load
Facility
,If(Facility = Peek(Facility), TimeStamp(StartDate + IterNo() - 1), StartDate) as StartDate
,If(StartDate + IterNo() < StopDate, TimeStamp(StartDate + IterNo()), StopDate) as StopDate
Resident Temp

While Floor(StartDate) + IterNo() - 1 < StopDate;

Drop Table Temp;

The trouble is with this is it just seems to assign all the time to the start date rather than splitting it over the start and end date.

If anyone could help me I would be very appreciative.

Thank you,

Mike

[Kushal_Chawda]

@mgDev  how it is 3.5 hours for  09/06/2020?

[sunny_talwar]

Try this

Table:
LOAD [Work Center Name],
	 Date(Floor([Start Date]) + IterNo() - 1) as Date,
	 If(Floor([Start Date]) + IterNo() - 1 = Floor([Start Date]), Round(1 - Frac([Start Date]), 1/48) * 24,
	 	If(Floor([Start Date]) + IterNo() = Ceil([Stop Date]), Round(Frac([Stop Date]), 1/48) * 24, 24)) as Duration
While Floor([Start Date]) + IterNo() <= Ceil([Stop Date]);
LOAD * INLINE [
    Work Center Name, Start Date, Stop Date
    AA, 2020-06-09 19:34:11.34113411, 2020-06-10 09:29:45.29452945
    AB, 2020-06-09 19:34:11.34113411, 2020-06-15 09:29:45.29452945
];

[mgDev]

Hi Kush,

Apologies my mistake - should approx be 4.5

Thanks,

Mike

[sunny_talwar]

Did you try the script provided below? I believe it should work

[mgDev]

Hi Sunny,

Thank you so much for coming back to me - unfortunately this isn't working for my data set. This is the output from this query;

Name	Date	Duration
AA	09/06/2020	4.5
AA	09/06/2020	9
AA	10/06/2020	1.5
AA	10/06/2020	9.5
AA	10/06/2020	11.5
AA2	09/06/2020	9.5
AA2	10/06/2020	9.5
AA2	10/06/2020	18

This is the source data 

Work Center Name	Start Date	Stop Date
AA	2020-06-09 15:13:41.13411341	2020-06-09 17:55:25.55255525
AA	2020-06-09 19:34:11.34113411	2020-06-10 09:29:45.29452945
AA	2020-06-10 12:21:23.21232123	2020-06-10 12:32:53.32533253
AA	2020-06-10 22:19:54.19541954	2020-06-11 06:05:45.545545
AA2	2020-06-09 14:42:09.429429	2020-06-09 15:17:56.17561756
AA2	2020-06-10 06:04:27.427427	2020-06-10 08:03:24.324324

What I think I should be getting is something more akin to the approximate numbers below;

Work Center Name	Date	Duration (Approx)
AA	09/06/2020	7.12
AA	10/06/2020	11.40
AA	11/06/2020	6.00
AA2	09/06/2020	0.50
AA2	10/06/2020	2.00

Thank you for your help,

Mike

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD [Work Center Name],
	 Date(Floor([Start Date]) + IterNo() - 1) as Date,
	 If(Floor([Start Date]) = Floor([Stop Date]), Round(Frac([Stop Date]) - Frac([Start Date]), 1/48) * 24,
	 	If(Floor([Start Date]) + IterNo() - 1 = Floor([Start Date]), Round(1 - Frac([Start Date]), 1/48) * 24,
	 		If(Floor([Start Date]) + IterNo() = Ceil([Stop Date]), Round(Frac([Stop Date]), 1/48) * 24, 24))) as Duration
While Floor([Start Date]) + IterNo() <= Ceil([Stop Date]);
LOAD * INLINE [
    Work Center Name, Start Date, Stop Date
    AA, 2020-06-09 15:13:41.13411341, 2020-06-09 17:55:25.55255525
    AA, 2020-06-09 19:34:11.34113411, 2020-06-10 09:29:45.29452945
    AA, 2020-06-10 12:21:23.21232123, 2020-06-10 12:32:53.32533253
    AA, 2020-06-10 22:19:54.19541954, 2020-06-11 06:05:45.545545
    AA2, 2020-06-09 14:42:09.429429, 2020-06-09 15:17:56.17561756
    AA2, 2020-06-10 06:04:27.427427, 2020-06-10 08:03:24.324324
];

and then you can just use Work Center Name and Date as dimension and Sum(Duration) as Measure. Also, not sure if you need rounding or not, but feel free to remove it if you think it is not needed.

Table:
LOAD [Work Center Name],
	 Date(Floor([Start Date]) + IterNo() - 1) as Date,
	 If(Floor([Start Date]) = Floor([Stop Date]), (Frac([Stop Date]) - Frac([Start Date])) * 24,
	 	If(Floor([Start Date]) + IterNo() - 1 = Floor([Start Date]), (1 - Frac([Start Date])) * 24,
	 		If(Floor([Start Date]) + IterNo() = Ceil([Stop Date]), (Frac([Stop Date])) * 24, 24))) as Duration
While Floor([Start Date]) + IterNo() <= Ceil([Stop Date]);
LOAD * INLINE [
    Work Center Name, Start Date, Stop Date
    AA, 2020-06-09 15:13:41.13411341, 2020-06-09 17:55:25.55255525
    AA, 2020-06-09 19:34:11.34113411, 2020-06-10 09:29:45.29452945
    AA, 2020-06-10 12:21:23.21232123, 2020-06-10 12:32:53.32533253
    AA, 2020-06-10 22:19:54.19541954, 2020-06-11 06:05:45.545545
    AA2, 2020-06-09 14:42:09.429429, 2020-06-09 15:17:56.17561756
    AA2, 2020-06-10 06:04:27.427427, 2020-06-10 08:03:24.324324
];

[mgDev]

Hi Sunny,

This is working perfectly - thank you soo much! Really appreciated.

Thanks again,

Mike