topic Re: How to calculate hours in date range in App Development

How to calculate hours in date range

gleybson — Fri, 12 Aug 2022 15:16:17 GMT

I have several notes that start one day and end the next. I need to calculate and the rule is start greater than 16h and end less than 16h.
Example:
func, oper, dt ini time, dt end time,
A, 1, 06/26/2022 22:00, 06/26/2022 22:50
A, 2, 06/27/2022 01:00, 06/27/2022 04:30
A, 3, 06/27/2022 17:00, 06/27/2022 22:00
A, 4, 06/28/2022 20:00, 06/28/2022 23:00
A, 5, 06/29/2022 03:00, 06/29/2022 14:00
B, 1, 06/26/2022 22:00, 06/26/2022 22:50
B, 2, 06/27/2022 01:00, 06/27/2022 04:30
B, 3, 06/27/2022 17:00, 06/27/2022 22:00
B, 4, 06/28/2022 20:00, 06/28/2022 23:00
B, 5, 06/29/2022 03:00, 06/29/2022 14:00

Desired result:
Func "A" on the 26th = from 22:00 on the 26th to 04:30 on the 27th
func, day, total hours
A, 26 06:30
A, 27 05:00
A, 28 18:00

Re: How to calculate hours in date range

Aurelien_Martinez — Fri, 12 Aug 2022 15:59:45 GMT

Hi,

I have tryed this :

Data:
Load
*,
If(Hour([dt ini time]) <=16, 1, 0) as Flg_ini,
If(Hour([dt ini time]) >=16, 1, 0) as Flg_end
;
Load
func,
oper,
Timestamp#([dt ini time], 'MM/DD/YYYY hh:mm') as [dt ini time],
Timestamp#([dt end time], 'MM/DD/YYYY hh:mm') as [dt end time]
Inline [
func, oper, dt ini time, dt end time,
A, 1, 06/26/2022 22:00, 06/26/2022 22:50
A, 2, 06/27/2022 01:00, 06/27/2022 04:30
A, 3, 06/27/2022 17:00, 06/27/2022 22:00
A, 4, 06/28/2022 20:00, 06/28/2022 23:00
A, 5, 06/29/2022 03:00, 06/29/2022 14:00
B, 1, 06/26/2022 22:00, 06/26/2022 22:50
B, 2, 06/27/2022 01:00, 06/27/2022 04:30
B, 3, 06/27/2022 17:00, 06/27/2022 22:00
B, 4, 06/28/2022 20:00, 06/28/2022 23:00
B, 5, 06/29/2022 03:00, 06/29/2022 14:00
];

Load
*,
If(Peek(Flg_ini)=1 and Flg_end=1,
Peek([dt ini time])
) as toto
Resident Data
Order By
func,
[dt ini time] desc
;

Drop Table Data;

Aurélien

Re: How to calculate hours in date range

gleybson — Tue, 16 Aug 2022 12:45:26 GMT

What does this "toto" field mean?