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
(DateEmployed >= @StartDate AND DateEmployed <= @EndDate) /*Covers 4,5,6 */
(LeavingDate >= @StartDate AND LeavingDate >= @EndDate) /*Covers 2, 4*/
(DateEmployed <= @EndDate AND (LeavingDate >= @StartDate or LeavingDate IS NULL)) /* Covers 3*/
@StartDate = 01/03/2012
@LeavingDate = 31/03/2012
|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|