To fetch records between two date range

[#1]

Hi, 

I have the following data table with the first 4 columns being the input and last 2 being the output required. I tried to join two tables to combine column 1-3 and column 4 by matching ID = ID and Adm.Date = Ref.Date. Now I want two output columns (Adm. Date_1 and Dis_Date_1) as shown below. I need the Adm.Date and Dis.Date populated for all the Ref.Date between range of Adm.Date and Dis.Date and missing value for those which does not fit into the date range. I was able to get respective Adm. Date and Dis.Date using if and lag function only for next following record and not for all the records in that range. Please let me know if there's any way to find an appropriate solution to this problem.

Thanks in advance.

ID	Adm. Date	Dis.Date	Ref.Date	Adm.Date_1	Dis.Date_1
12	2/12/2014	15/12/2014	2/12/2014	2/12/2014	15/12/2014
12			3/12/2014	2/12/2014	15/12/2014
12			6/12/2014	2/12/2014	15/12/2014
13			10/1/2015
13	6/1/2016	25/1/2016	6/1/2016	6/1/2016	25/1/2016
13			10/1/2016	6/1/2016	25/1/2016
13			11/1/2016	6/1/2016	25/1/2016
13			12/1/2016	6/1/2016	25/1/2016

[#2]

Here's a solution that worked on your test case by looping through the rows in the table.  Let me know if you need help understanding the code.

dt = Current Data Table();

ad_date_temp = .;
dis_date_temp = .;

for(i=1,i<=N Row(dt), i++,
	//Only update these vars if current row has new values for them
	ad_date_temp = If(IsMissing(:Name("Adm. Date")[i]),ad_date_temp, :Name("Adm. Date")[i]);
	dis_date_temp = If(IsMissing(:Name("Dis. Date")[i]),dis_date_temp, :Name("Dis. Date")[i]);
	
	Show(i, ad_date_temp, dis_date_temp);
	If(:Name("Ref.Date")[i] >= ad_date_temp & :Name("Ref.Date")[i] <= dis_date_temp,
		//Execute if Ref Date is in the current range
			:Name("Adm.Date_1")[i] = ad_date_temp;
			:Name("Dis.Date_1")[i] = dis_date_temp
		,
		//Execute if Ref Date is not in the current range
			:Name("Adm.Date_1")[i] = .;
			:Name("Dis.Date_1")[i] = .
	);
);

[#3]

Hi Wilden,

Thank you for your solution. 

I want the Ref.Date to be in the respective date range for that particular ID. Lets say for row 4 (ID 13), Ref.Date is between Adm.Date and Dis.Date of ID 12, so it should be "." instead of taking values of ID 12. Ideally, date should be populated based on ID and not on the previous Adm.Date and Dis.Date record available. I am not sure if this script will take care of this requirement. Appreciate your help in this. Thanks.

Rini

ID	Adm. Date	Dis.Date	Ref.Date	Adm.Date_1	Dis.Date_1
12	2/12/2014	15/12/2014	2/12/2014	2/12/2014	15/12/2014
12			3/12/2014	2/12/2014	15/12/2014
12			6/12/2014	2/12/2014	15/12/2014
13			13/12/2014
13			10/1/2015
13	6/1/2016	25/1/2016	6/1/2016	6/1/2016	25/1/2016
13			10/1/2016	6/1/2016	25/1/2016
13			11/1/2016	6/1/2016	25/1/2016
13			12/1/2016	6/1/2016	25/1/2016

[#4 · Accepted solution]

You can accommodate that pretty easily with some logical checks on ID:

dt = Current Data Table();

ad_date_temp = .;
dis_date_temp = .;
id_temp = :ID[1];

for(i=1,i<=N Row(dt), i++,
	//Only update these vars if current row has new values for them
	
	If(!Is Missing(:Name("Adm. Date")[i]),
		//Execute if new Adm. Date
			ad_date_temp = :Name("Adm. Date")[i];
			dis_date_temp = :Name("Dis. Date")[i];
			id_temp = :ID[i],
	);
	
	If(:Name("Ref.Date")[i] >= ad_date_temp & :Name("Ref.Date")[i] <= dis_date_temp & :ID[i] == id_temp,
		//Execute if Ref Date is in the current range
			:Name("Adm.Date_1")[i] = ad_date_temp;
			:Name("Dis.Date_1")[i] = dis_date_temp
		,
		//Execute if Ref Date is not in the current range
			:Name("Adm.Date_1")[i] = .;
			:Name("Dis.Date_1")[i] = .
	);
);

[#5]

Thank you for the solution. It worked perfectly.