topic Re: Split Start Date and End Date into Hours Per Days in App Development

Split Start Date and End Date into Hours Per Days

mgDev — Sat, 16 Nov 2024 01:25:28 GMT

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

Re: Split Start Date and End Date into Hours Per Days

Kushal_Chawda — Mon, 21 Sep 2020 16:30:20 GMT

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

Re: Split Start Date and End Date into Hours Per Days

sunny_talwar — Mon, 21 Sep 2020 19:27:26 GMT

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 ];

Re: Split Start Date and End Date into Hours Per Days

mgDev — Tue, 22 Sep 2020 10:58:27 GMT

Hi Kush,

Apologies my mistake - should approx be 4.5

Thanks,

Mike

Re: Split Start Date and End Date into Hours Per Days

sunny_talwar — Tue, 22 Sep 2020 11:05:48 GMT

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

Re: Split Start Date and End Date into Hours Per Days

mgDev — Tue, 22 Sep 2020 11:09:03 GMT

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

Re: Split Start Date and End Date into Hours Per Days

sunny_talwar — Tue, 22 Sep 2020 11:35:13 GMT

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 ];

Re: Split Start Date and End Date into Hours Per Days

mgDev — Tue, 22 Sep 2020 12:13:19 GMT

Hi Sunny,

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

Thanks again,

Mike