Interval match with two ranges

[Rajkumar]

Hi All,

I have scenario where my hour and date have to match with two ranges from interval match table.

table1:

id  ,date, hour

1,  2019-12-01,  10

1 , 2020 -04-01 , 13

table2:

id  datestart ,dateend, timestart,time end,

1 2018-01-01,2020-01-01, 10 ,23

1 2020-01-02,9999-01-01 ,12,22

how do i map this two ranges into table1?

please help

id 

[Taoufiq_Zarra]

Maye be :

table1:

load *,TimeStamp(Date#(date,'YYYY-MM-DD')+maketime(hour,0,0)) as new_date inline [
id  ,date, hour

1,  2019-12-01, 10

1 , 2020-04-01 , 13
];


table2:
load id as idtable2, TimeStamp(Date#(datestart,'YYYY-MM-DD')+maketime(timestart,0,0)) as new_start,TimeStamp(Date#(dateend,'YYYY-MM-DD')+maketime("time end",0,0)) as new_end inline [
id ,datestart ,dateend, timestart,time end,

1,2018-01-01,2020-01-01, 10 ,23

1,2020-01-02,9999-01-01 ,12,22
];

Inner Join IntervalMatch ( new_date ) 
LOAD new_start, new_end
Resident table2;

[Rajkumar]

Thanks for quick reply,

According to this logic,it will take all the hours between two dates irrespective of condition 

[Taoufiq_Zarra]

if it' s not what you need, please give me more details about the requirements