Time and Loops in Script

[JT1]

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!

[chrismarlow · Accepted solution]

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.

[JT1]

Thanks this works perfectly !