Interval match within multiple instances

[lionking15]

Hi Team,

I am struggling to crack below given logic, Where we need to get the interval match with multiple time instances

Need to create bucket(i.e. interval) on the basis of start and end

Input Table:

Job Id	Start	End
1	08:15am	11:13am

Output Table:

Job Id	Start	End	Interval
1	08:15am	11:13am	8am
1	08:15am	11:13am	9am
1	08:15am	11:13am	10am
1	08:15am	11:13am	11am

[Qbo]

I'd try doing it the other way around, should get you the result you want if you create the interval hours and match them in.

For example:

IntervalHours:
LOAD time(Hours,'hh:mm') as Hours Inline[
Hours
01:00
02:00
03:00
04:00
05:00
06:00
07:00
08:00
09:00
10:00
11:00
12:00
13:00
14:00
15:00
16:00
17:00
18:00
19:00
20:00
21:00
22:00
23:00
];

OrderLog:
LOAD time(Start,'hh:mm') as Start,
	 time(End,'hh:mm') as End,
	 Job_Id as [Job Id] INLINE [
Start, End, Job_Id
08:15, 11:13, 1
];

Inner Join IntervalMatch (Hours)
 
LOAD Start, End
Resident OrderLog;

[justISO · Accepted solution]

Hi, I have other solution, but not sure how it will work with am, pm format. Maybe you can reuse it to your solution:

temp:
load * inline [
JobId, 	Start, 	End,
1, 	08:15 ,	11:13
2 ,  09:00, 14:00	
];

NoConcatenate
main_temp:

load * resident temp;
concatenate(main_temp) 
load distinct JobId, Start, End,
              hour(Start)+iterno()-1 as Interval
       resident temp while hour(Start) + iterno()-1 <= hour(End);
       
NoConcatenate
main:
load * resident main_temp
where not IsNull(Interval);

drop table temp,main_temp;

[lionking15]

Thanks @justISO It worked for me😀