Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
Looking for some scripting help. I have the following fields in a table (100 records)
OriginalTable:
ID | Date | StartTime | EndTime
1 | 4/1/2019 | 14:30:00 | 16:30:00
2 | 4/1/2019 | 15:25:10 | 15:55:27
3 | 4/1/2019 | 14:12:21 | 17:20:27
I need to loop through this table and calculate what percentage of each hour is occupied between StartTime and EndTime.
So for example the ID 1 in the original table will have 3 rows in the new table
NewTable:
ID | NewID | Hour | %Occupied
1 | 1-1 | 14 | 50%
1 | 1-2 | 15 | 100%
1 | 1-3 | 16 | 50%
Any help would be appreciated!
Hi,
I do like an interval match problem … but I thought this was going to be easier than this 😀. Try the below;
data:
Load
Timestamp(Date+StartTime-(1/24)) as StartDateTime,
Timestamp(Date+EndTime) as EndDateTime,
*
;
LOAD * INLINE [
ID , Date , StartTime , EndTime
1 , 4/1/2019, 14:30:00 , 16:30:00
2 , 4/1/2019 , 15:25:10 , 15:55:27
3 , 4/1/2019, 14:12:21 , 17:20:27
];
intervals:
Load
IterNo() AS Interval,
TimeStamp(DMin + ((IterNo()-2)/24)) as Hour
While DMin+((IterNo()-2)/24) <= DMax
;
Load
Min(RoundedDateStartTime) AS DMin,
Max(RoundedDateEndTime) AS DMax;
Load
Timestamp(Date+floor(StartTime, 1/24)) AS RoundedDateStartTime,
Timestamp(Date+floor(EndTime, 1/24)) AS RoundedDateEndTime
Resident data;
join IntervalMatch(Hour)
Load
StartDateTime,
EndDateTime
resident data;
left join (data)
Load
*
resident intervals;
drop table intervals;
data_final:
NoConcatenate
LOAD
ID,
ID2,
Date,
StartTime,
EndTime,
Hour,
If(StartDateTime+(1/24)<Hour AND EndDateTime>Hour+(1/24),
1,
If(ID2=1,
If(EndDateTime>Hour+(1/24),
24*(Hour-StartDateTime),
24*(EndDateTime-(StartDateTime+(1/24)))
),
24*(EndDateTime-Hour)
)
) AS Proportion;
Load
If(RowNo()=1,
1,
If(Peek('ID')=ID,
Peek('ID2')+1,
1
)
) as ID2,
*
Resident data
Order By ID, Hour;
drop table data;
Cheers,
Chris.
Hi,
I do like an interval match problem … but I thought this was going to be easier than this 😀. Try the below;
data:
Load
Timestamp(Date+StartTime-(1/24)) as StartDateTime,
Timestamp(Date+EndTime) as EndDateTime,
*
;
LOAD * INLINE [
ID , Date , StartTime , EndTime
1 , 4/1/2019, 14:30:00 , 16:30:00
2 , 4/1/2019 , 15:25:10 , 15:55:27
3 , 4/1/2019, 14:12:21 , 17:20:27
];
intervals:
Load
IterNo() AS Interval,
TimeStamp(DMin + ((IterNo()-2)/24)) as Hour
While DMin+((IterNo()-2)/24) <= DMax
;
Load
Min(RoundedDateStartTime) AS DMin,
Max(RoundedDateEndTime) AS DMax;
Load
Timestamp(Date+floor(StartTime, 1/24)) AS RoundedDateStartTime,
Timestamp(Date+floor(EndTime, 1/24)) AS RoundedDateEndTime
Resident data;
join IntervalMatch(Hour)
Load
StartDateTime,
EndDateTime
resident data;
left join (data)
Load
*
resident intervals;
drop table intervals;
data_final:
NoConcatenate
LOAD
ID,
ID2,
Date,
StartTime,
EndTime,
Hour,
If(StartDateTime+(1/24)<Hour AND EndDateTime>Hour+(1/24),
1,
If(ID2=1,
If(EndDateTime>Hour+(1/24),
24*(Hour-StartDateTime),
24*(EndDateTime-(StartDateTime+(1/24)))
),
24*(EndDateTime-Hour)
)
) AS Proportion;
Load
If(RowNo()=1,
1,
If(Peek('ID')=ID,
Peek('ID2')+1,
1
)
) as ID2,
*
Resident data
Order By ID, Hour;
drop table data;
Cheers,
Chris.
Thanks this works perfectly !