how to show records which matched with selected date from master calendar

[Anonymous]

In example I have 4 records with different start date and end date, as below:

Policy	start	end
123456	15/01/2014	14/08/2014
456789	20/02/2014	21/05/2014
789123	25/03/2014	24/07/2014
123789	30/04/2014	29/08/2014

Question: what should I do if I want to be able to select any date using master calendar, and the result table should only showing those records start before AND end after the selected date.

Hopefully the following scenario will help to understand the above logic:

1. If I change the date selection to become 19 Feb 2014 from the master calendar, then only the first record should be shown as the result. The reason is the first record start date is before 19 Feb 2014 and end after 19 Feb 2014

Policy	start	end
123456	15/01/2014	14/08/2014

2. If I change the date selection to become 20 May 2014 from the master calendar, then all the above records should be shown as the result. The reason is because all of the records start date are before 20 May 2014 and end after 20 May 2014.

Policy	start	end
123456	15/01/2014	14/08/2014
456789	20/02/2014	21/05/2014
789123	25/03/2014	24/07/2014
123789	30/04/2014	29/08/2014

3. If I change the date selection to become 26 Jul 2014 from the master calendar, then only two records should be shown as the result. The reason is only the following records start date before 26 Jul 2014 and end after 26 Jul 2014.

Policy	start	end
123456	15/01/2014	14/08/2014
123789	30/04/2014	29/08/2014

[tresesco]

Have a look at IntervalMatch().

[vikasmahajan]

hope this helps you in understanding Interval Match.

Vikas

[MK_QSL · Accepted solution]

Use below in your script..

==================================

POLICY:

Load * Inline

[

Policy, Start, End

123456, 15/01/2014, 14/08/2014

456789, 20/02/2014, 21/05/2014

789123, 25/03/2014, 24/07/2014

123789, 30/04/2014, 29/08/2014

];

TempDateRange:

Load

  RangeMin(Min(Start),Min(End)) as MinDate,

  RangeMax(Max(Start),Max(End)) as MaxDate

Resident POLICY;

Let vMinDate = Num(Peek('MinDate',0,'TempDateRange'));

Let vMaxDate = Num(Peek('MaxDate',0,'TempDateRange'));

Drop Table TempDateRange;

DateCalendar:

Load

  Date($(vMinDate)+RowNo()-1) as Date

AutoGenerate $(vMaxDate)-$(vMinDate)+1;

Join

IntervalMatch(Date)

Load Start, End Resident POLICY;

Left Join(DateCalendar) Load * Resident POLICY;

Drop Table POLICY;

==================================

[Anonymous]

Thank you very much. Your script solved my question.