Intervalmatch

[AleRods88]

Hello,

I have the following table

SHIP	PERIOD	START_DATE	END_DATE	VALUE
2803845	201802	4/2/2018	3/2/2019	3
2803845	201802	4/2/2019	3/1/2020	4
2803845	201802	4/1/2020	1/3/2020	1
2803845	201803	4/2/2018	3/2/2019	3
2803845	201803	4/2/2019	3/1/2020	4
2803845	201803	4/1/2020	1/3/2020	1
2803845	201804	4/2/2018	3/2/2019	3
2803845	201804	4/2/2019	3/1/2020	4
2803845	201804	4/1/2020	1/3/2020	1
2803845	201805	4/2/2018	3/2/2019	3
2803845	201805	4/2/2019	3/1/2020	4
2803845	201805	4/1/2020	1/3/2020	1
2803845	201806	4/2/2018	3/2/2019	3
2803845	201806	4/2/2019	3/1/2020	4
2803845	201806	4/1/2020	1/3/2020	1
2803845	201807	4/2/2018	3/2/2019	3
2803845	201807	4/2/2019	3/1/2020	4
2803845	201807	4/1/2020	1/3/2020	1
2803845	201808	4/2/2018	3/2/2019	3
2803845	201808	4/2/2019	3/1/2020	4
2803845	201808	4/1/2020	1/3/2020	1
2803845	201809	4/2/2018	3/2/2019	3
2803845	201809	4/2/2019	3/1/2020	4
2803845	201809	4/1/2020	1/3/2020	1
2803845	201810	4/2/2018	3/2/2019	3
2803845	201810	4/2/2019	3/1/2020	4
2803845	201810	4/1/2020	1/3/2020	1
2803845	201811	4/2/2018	3/2/2019	3
2803845	201811	4/2/2019	3/1/2020	4
2803845	201811	4/1/2020	1/3/2020	1
2803845	201812	4/2/2018	3/2/2019	3
2803845	201812	4/2/2019	3/1/2020	4
2803845	201812	4/1/2020	1/3/2020	1
2803845	201901	4/2/2018	3/2/2019	3
2803845	201901	4/2/2019	3/1/2020	4
2803845	201901	4/1/2020	1/3/2020	1
2803845	201902	4/2/2018	3/2/2019	3
2803845	201902	4/2/2019	3/1/2020	4
2803845	201902	4/1/2020	1/3/2020	1
2803845	201903	4/2/2018	3/2/2019	3
2803845	201903	4/2/2019	3/1/2020	4
2803845	201903	4/1/2020	1/3/2020	1
2803845	201904	4/2/2018	3/2/2019	3
2803845	201904	4/2/2019	3/1/2020	4
2803845	201904	4/1/2020	1/3/2020	1
2803845	201905	4/2/2018	3/2/2019	3
2803845	201905	4/2/2019	3/1/2020	4
2803845	201905	4/1/2020	1/3/2020	1
2803845	201906	4/2/2018	3/2/2019	3
2803845	201906	4/2/2019	3/1/2020	4
2803845	201906	4/1/2020	1/3/2020	1
2803845	201907	4/2/2018	3/2/2019	3
2803845	201907	4/2/2019	3/1/2020	4
2803845	201907	4/1/2020	1/3/2020	1
2803845	201908	4/2/2018	3/2/2019	3
2803845	201908	4/2/2019	3/1/2020	4
2803845	201908	4/1/2020	1/3/2020	1
2803845	201909	4/2/2018	3/2/2019	3
2803845	201909	4/2/2019	3/1/2020	4
2803845	201909	4/1/2020	1/3/2020	1
2803845	201910	4/2/2018	3/2/2019	3
2803845	201910	4/2/2019	3/1/2020	4
2803845	201910	4/1/2020	1/3/2020	1
2803845	201911	4/2/2018	3/2/2019	3
2803845	201911	4/2/2019	3/1/2020	4
2803845	201911	4/1/2020	1/3/2020	1
2803845	201912	4/2/2018	3/2/2019	3
2803845	201912	4/2/2019	3/1/2020	4
2803845	201912	4/1/2020	1/3/2020	1
2803845	202001	4/2/2018	3/2/2019	3
2803845	202001	4/2/2019	3/1/2020	4
2803845	202001	4/1/2020	1/3/2020	1
2803845	202002	4/2/2018	3/2/2019	3
2803845	202002	4/2/2019	3/1/2020	4
2803845	202002	4/1/2020	1/3/2020	1
2803845	202003	4/2/2018	3/2/2019	3
2803845	202003	4/2/2019	3/1/2020	4
2803845	202003	4/1/2020	1/3/2020	1

What I need to do is compare if the Period is in the range Start_Date/End_Date and bring a single row per Period with the Value associated to that Period:

SHIP	PERIOD	START_DATE	END_DATE	VALUE
2803845	201802	4/2/2018	3/2/2019	3
2803845	201803	4/2/2018	3/2/2019	3
2803845	201804	4/2/2018	3/2/2019	3
2803845	201805	4/2/2018	3/2/2019	3
2803845	201806	4/2/2018	3/2/2019	3
2803845	201807	4/2/2018	3/2/2019	3
2803845	201808	4/2/2018	3/2/2019	3
2803845	201809	4/2/2018	3/2/2019	3
2803845	201810	4/2/2018	3/2/2019	3
2803845	201811	4/2/2018	3/2/2019	3
2803845	201812	4/2/2018	3/2/2019	3
2803845	201901	4/2/2018	3/2/2019	3
2803845	201902	4/2/2018	3/2/2019	3
2803845	201903	4/2/2019	3/1/2020	4
2803845	201904	4/2/2019	3/1/2020	4
2803845	201905	4/2/2019	3/1/2020	4
2803845	201906	4/2/2019	3/1/2020	4
2803845	201907	4/2/2019	3/1/2020	4
2803845	201908	4/2/2019	3/1/2020	4
2803845	201909	4/2/2019	3/1/2020	4
2803845	201910	4/2/2019	3/1/2020	4
2803845	201911	4/2/2019	3/1/2020	4
2803845	201912	4/2/2019	3/1/2020	4
2803845	202001	4/1/2020	1/3/2020	1
2803845	202002	4/1/2020	1/3/2020	1
2803845	202003	4/1/2020	1/3/2020	1

Is there any way to do that?

Thank you!!

[Taoufiq_Zarra]

can you elaborate how you get the second table for the first ? examples

[MayilVahanan]

Hi @AleRods88 

Try like this

Load * where Date >= START_DATE and Date <= END_DATE;
LOAD *, MakeDate(Left(PERIOD,4), right(PERIOD,2),28) as Date INLINE [
SHIP, PERIOD, START_DATE, END_DATE, VALUE
2803845, 201802, 4/2/2018, 3/2/2019, 3
2803845, 201802, 4/2/2019, 3/1/2020, 4
2803845, 201802, 4/1/2020, 1/3/2020, 1
2803845, 201803, 4/2/2018, 3/2/2019, 3
2803845, 201803, 4/2/2019, 3/1/2020, 4
2803845, 201803, 4/1/2020, 1/3/2020, 1
2803845, 201804, 4/2/2018, 3/2/2019, 3
2803845, 201804, 4/2/2019, 3/1/2020, 4
2803845, 201804, 4/1/2020, 1/3/2020, 1
2803845, 201805, 4/2/2018, 3/2/2019, 3
2803845, 201805, 4/2/2019, 3/1/2020, 4
2803845, 201805, 4/1/2020, 1/3/2020, 1
2803845, 201806, 4/2/2018, 3/2/2019, 3
2803845, 201806, 4/2/2019, 3/1/2020, 4
2803845, 201806, 4/1/2020, 1/3/2020, 1
];