How can I show a set of matching records between two dates when users can select any date combination?

[#1]

Hi,

We have a set of people on our system who have a start date and a leave date. Our users want to see who was "live" at any point in the past, based on month and year combinations. They also want the data broken down by month/year.

I can't think of a way to do this, as they could pick (for example) 2010 May, June and October and 2011 January. So the chart would need to show those four months only (not a problem), but also show the people who were live in those months based on their start/end date.

Any ideas?

Many thanks

Nick

[#2]

Hi Nick.

First you will need to create all possible dates between the lowest date (in both fields - start and leave date) and highiest date (in both of them to);

Example:

TableXXX:

Load

PersonID,

StartDate,

LeaveDate;

SQL Select xxxxxxxxx From yyyyyy;

[MinDate]:

Load Min(StartDate) as MinStartDate

Resident TableXXX;

[MaxDate]:

Load Max(LeaveDate) as MaxLeaveDate

Resident TableXXX;

// YYYY-MM-DD = ISO standard format for dates (most compatible)

Let Var_MinDate = Date(Peek('MinStartDate', 0, 'MinDate'), 'YYYY-MM-DD');

Let Var_MaxDate = Date(Peek('MaxLeaveDate', 0, 'MaxDate'), 'YYYY-MM-DD');

Drop tables MinDate, MaxDate;

//Now you have the highiest and the lowest dates... you will need to fill this interval.

// Example

[DATESBETWEEN]:

Load

'$(Var_MinDate)' + RowNo() as [Dates]

AutoGenerate($(Var_MaxDate) - $(Var_MinDate));

// Now You have a Table DATESBETWEEN With all possible dates. Now you will make a Cartesian product of DATESBETWEEN and TableXXX.

join (TableXXX)

Load Dates

Resident DATESBETWEEN;

Drop Table DATESBETWEE;

//And finally, you wil need to filter your Cartesian Product (on TableXXX) between the StartDate and LeaveDate;

//Example

[TableResult]:

Load * Resident TableXXX Where Dates >= StartDate and Dates <= LeaveDate;

Drop Table TableXXX;

Thats the idea... In the end, the table 'TableResult' will have the time between the start and leave date.

Good Luck,

Bruno

[#3]

Hi Bruno,

Thanks for the reply. I get the building of a table of all possible dates and joining that to the master table. It's your final bit of the solution that I'm not sure about.

You're explicitly putting in the load all dates >= start date and <= leave date. I can't do this as users can change this in the app to pick any combination of months and even years. So I don't know at the load point what dates are going to be picked!

[#4]

Yes, thats why we needed to make a cartesian product and, after that, make a filter explicitaly like you said, filling the intervals of start and leave dates.

1) You Had:

TempXXX

People StartDate LeaveDate

John 2010-01-01 2010-01-03

Peter 2010-01-02 2010-01-02

Mary 2010-01-02 2010-01-05

2) We made dates between the min(StartDate) = 2010-01-01 and Max(LeaveDate) = 2010-01-05

Dates

2010-01-01

2010-01-02

2010-01-03

2010-01-04

2010-01-05

3) Then we joinned with the master table...

TempXXX

People StartDate LeaveDate Dates

John 2010-01-01 2010-01-03 2010-01-01

John 2010-01-01 2010-01-03 2010-01-02

John 2010-01-01 2010-01-03 2010-01-03

John 2010-01-01 2010-01-03 2010-01-04

John 2010-01-01 2010-01-03 2010-01-05

Peter 2010-01-02 2010-01-02 2010-01-01

Peter 2010-01-02 2010-01-02 2010-01-02

Peter 2010-01-02 2010-01-02 2010-01-03

Peter 2010-01-02 2010-01-02 2010-01-04

Peter 2010-01-02 2010-01-02 2010-01-05

Mary 2010-01-02 2010-01-05 2010-01-01

Mary 2010-01-02 2010-01-05 2010-01-02

Mary 2010-01-02 2010-01-05 2010-01-03

Mary 2010-01-02 2010-01-05 2010-01-04

Mary 2010-01-02 2010-01-05 2010-01-05

4) Then we explicitly filter the field "Dates" between the StartDate and LeaveDate (Dates >= StartDate and Dates <= LeaveDate)

And we will have:

TempXXX

People StartDate LeaveDate Dates

John 2010-01-01 2010-01-03 2010-01-01

John 2010-01-01 2010-01-03 2010-01-02

John 2010-01-01 2010-01-03 2010-01-03

Peter 2010-01-02 2010-01-02 2010-01-02

Mary 2010-01-02 2010-01-05 2010-01-02

Mary 2010-01-02 2010-01-05 2010-01-03

Mary 2010-01-02 2010-01-05 2010-01-04

Mary 2010-01-02 2010-01-05 2010-01-05

After doing what I suggested, you would use the field "Dates" often StartDate or LeaveDate in the layout...

if someone select 2010-01-02 it will show that John, Mary and Peter was live..

If someone select 2010-01-01 and 2010-01-03 it will show that John, Mary was live.. not Peter because he wont...

Wasn't that the main problem? If not, sorry... I'd understand wrong...

Regards,

Bruno

[Oleg_Troyansky]

Nick,

I assume you have a Calendar with dates or Months that the users can select... You could link the Date from the Calendar with the pairs of Start/Leave Dates using INTERVALMATCH (if it doesn't interfere with the rest of your data model). If you can do that, the rest will happen auto-magically 🙂