Reporting from date range parameters

So here’s one.

You’ve got a table with employee ID primary key, a date employed and a leaving date which can be NULL. The start date has a NOT NULL constraint.

On a report you want to be able to enter a @StartDate and @EndDate parameter to return a list of employees that were employees during that date range. Note I didn’t say “employed during that date” range because that could be ambiguous and suggest that they began their employement during that period. That’s not what is required, rather the report needs to show anyone who was “current” at any time during the search range. Again, note I didn’t say “working during that period” because I’ve seen plenty of people who were employed but barely working… :p

Anyway, how’s this logic? I feel like it might be a bit agricultural or may have a flaw somewhere. Tim?

 

“Where” clause of the query

WHERE

(DateEmployed >= @StartDate AND DateEmployed <= @EndDate) /*Covers 4,5,6 */

OR

(LeavingDate >= @StartDate AND LeavingDate >= @EndDate) /*Covers 2, 4*/

OR

(DateEmployed <= @EndDate AND (LeavingDate >= @StartDate or LeavingDate IS NULL)) /* Covers 3*/

 

 Parameter Values

@StartDate = 01/03/2012

@LeavingDate = 31/03/2012

 

Example Data

Employee ID Date Employed Leaving Date Should be returned Is returned
1 10/01/2011 02/02/2012 No, started and finished before the search range Not covered
2 10/01/2011 15/03/2012 Yes, finished during the search range Yes
3 10/01/2011 NULL Yes, still employed Yes
4 12/03/2012 13/03/2012 Yes, started during the search range, also finished during the search range Yes
5 12/03/2012 15/04/2012 Yes, started during the search range Yes
6 12/03/2012 NULL Yes, started during the search range Yes
7 15/04/2012 18/09/2012 No, started after the search range Not covered
8 15/04/2012 NULL No, started after the search range Not covered

Anything missing?

3 thoughts on “Reporting from date range parameters

  1. Well, let’s call your solution “verbose”. I found it can be expressed with just three comparators.

    First, let’s only include employees that began work sometime before the period ends. We can cover the crowd that started before the period and those who started during the period with one clause: DateEmployed <= @EndDate.

    Next, we need to find the subset of these employees that were still working for us during the period. The first case is that they’re still working for us today, LeavingDate IS NULL. Alternately, they left our employ during or after the period, LeavingDate >= @StartDate.

    Thus, our clause is:

    WHERE DateEmployed <= @EndDate AND (LeavingDate >= @StartDate OR LeavingDate IS NULL)

    1. Thanks Tim! Certainly more elegant and less agricultural than my verboseness. This is all very familiar to a conversation we had a while back, thanks for the comment I really appreciate it.

Leave a Reply

Your email address will not be published. Required fields are marked *