Utilization per day per hour

[poortman]

Hi all,

I need some help with scripting. We've got patiënts in our nursing wards, with a StartDateTime  and a EndDateTime ('DD-MM-YYYY hh:mm'), like this:

AdmissionNo	StartDateTime	EndDateTime
1	02-01-2021 09:27	03-01-2021 10:51

For this AdmissionNo I want to see the utilization per day per hour, like this:

AdmissionNo	DateTime	DurationBucket
1	02-01-2021 09:00	31
1	02-01-2021 10:00	60
1	02-01-2021 11:00	60
..	..
1	03-01-2021 10:00	51

I already tried something, but this only works for when startdate is the same as the enddate..

Transactie:
Load *,

Iterno() as BucketID,
Hour(StartDateTime  +(iterno()-1)*MakeTime(1)) as Bucket_Hour,
Round((RangeMin(EndDateTime ,Ceil(StartDateTime  +(iterno()-1)*MakeTime(1), MakeTime(1)))
-RangeMax(StartDateTime  ,Floor(StartDateTime  +(iterno()-1)*MakeTime(1), MakeTime(1))))
*60*24) as DurationBucket
WHILE StartDateTime  +(iterno()-1)*MakeTime(1) <= Ceil(EndDateTime ,MakeTime(1));

Kind regards,

Niek Poortman

[poortman · Accepted solution]

Think I solved this one. Maybe it can be done in a easier way, but this seems to work. 

Here's the scripting I used.

Transactie:
Load *,
Date(Floor(StartDateTime),'DD-MM-YYYY') as Date,
Hour(StartDateTime) as Hour;
Load *,
TimeStamp(Timestamp(floor(StartDateTime,1/24),'DD-MM-YYYY hh')+(iterno()-1)*MakeTime(1),'DD-MM-YYYY hh') as DateTime,
Round((RangeMin(EndDateTime,Ceil(StartDateTime+(iterno()-1)*MakeTime(1), MakeTime(1)))
-RangeMax(StartDateTime,Floor(StartDateTime+(iterno()-1)*MakeTime(1), MakeTime(1))))
*60*24) as DurationBucket
While TimeStamp(Timestamp(floor(StartDateTime,1/24),'DD-MM-YYYY hh')+(iterno()-1)*MakeTime(1),'DD-MM-YYYY hh') <= Ceil(EndDateTime,MakeTime(1));
;