Posted by Plamen Ratchev on 04/24/07 22:23

It is a little bit unclear based on your tables to figure out how to
transform the days off to the schedule (what are those shifts, start/end
datetime, etc.). But here is a way to pull the schedule data together based
on your calendar table and the table with days off.

The first step is to normalize the DaysOff table. You can either redesign
the table and have it with only emp_id and dayoff columns and PK (emp_id,
dayoff), or if redesign is not possible then use a view, like this:

CREATE VIEW EmployeeDaysOff
(emp_id, dayoff)
AS
SELECT emp_id,
dayoff_1
FROM DaysOff
UNION ALL
SELECT emp_id,
dayoff_2
FROM DaysOff;

Then pulling the schedule based on your calendar table and this view becomes
a simple query:

DECLARE @start DATETIME
DECLARE @end DATETIME

SET @start = '20070429'
SET @end = '20070512'

SELECT E.emp_id,
C.date
FROM DaysOff AS E, CalendarDays AS C
WHERE C.date BETWEEN @start and @end
AND NOT EXISTS
(SELECT *
FROM EmployeeDaysOff AS O
WHERE O.emp_id = E.emp_id
AND O.dayoff = C.calendar_dow);

Notes:
- You can pass those parameters (@start and @end) to your stored procedure,
that will be the date range to open schedule for
- In the query I used DaysOff to get all employees. But you probably have a
table with employees and should replace it with that. The query above will
not produce the correct results if you have employees that do not have days
off.

If you are on SQL Server 2005 you can use the EXCEPT, like this:

SELECT E.emp_id,
C.date
FROM DaysOff AS E, CalendarDays AS C
WHERE C.date BETWEEN @start and @end
EXCEPT
SELECT O.emp_id,
C.date
FROM EmployeeDaysOff AS O, CalendarDays AS C
WHERE C.date BETWEEN @start and @end
AND O.dayoff = C.calendar_dow;

The same note to replacing DaysOff with the table with employees apply for
the here (for the first query before EXCEPT).

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Back to original message]