Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
==================================
Have a look at IntervalMatch().
hope this helps you in understanding Interval Match.
Vikas
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;
==================================
Thank you very much. Your script solved my question.