Data restrict between start and end date

[paulwalker]

Hi Community,

I have to restrict the data script level, 

Table1: 

Date	ID	Amount
31-12-2022	A1	18
10-04-2023	A1	23
13-02-2023	A1	55
14-01-2023	A1	10
31-12-2022	A2	18
10-04-2022	A2	45
13-02-2023	A2	23
14-01-2023	A2	90

Table2:

ID	Name	Start Date	End Date
A1	ABC	01-01-2023	20-06-2023
A2	BCA	01-01-2023	19-05-2023

Output Should be : based on start and end date I have to eliminate data script level (data should be display between the dates)

ex: for A1 data should be between 01-01-2023 and 20-06-2023

Date	ID	Amount
10-04-2023	A1	23
13-02-2023	A1	55
14-01-2023	A1	10
13-02-2023	A2	23
14-01-2023	A2	90

@tresesco, @MayilVahanan,  @Kushal_Chawda, @marcus_sommer, @stevedark and experts can you please look into this

Thanks in Advance!

[paulwalker]

Experts, could you please help on it 

[marcus_sommer · Accepted solution]

There are various ways possible - quite simple would be:

ExistsFilter: load ID & '|' & date(StartDate + iterno() -1) as Filter
from Table2 while StartDate + iterno() -1 <= EndDate;

t: load * from Table1 where exists(Filter, ID & '|' & Date);

[vincent_ardiet_]

With interval match for example:

SET DateFormat='DD-MM-YYYY';

Table1:
Load * inline
[
Date,ID,Amount
31-12-2022,A1,18
10-04-2023,A1,23
13-02-2023,A1,55
14-01-2023,A1,10
31-12-2022,A2,18
10-04-2022,A2,45
13-02-2023,A2,23
14-01-2023,A2,90];

Table2:
Load * inline
[
ID,Name,Start Date,End Date
A1,ABC,01-01-2023,20-06-2023
A2,BCA,01-01-2023,19-05-2023];

Inner Join (Table1) IntervalMatch (Date,ID)
Load [Start Date],[End Date], ID Resident Table2;

Drop Table Table2;