|
|
Posted by Plamen Ratchev on 04/25/07 03:01
In addition, if you meant to have the data into the schedule table by
listing the ranges of dates between the days off for each employee, you can
get the data like this (ROW_NUMBER assumes SQL Server 2005 is used):
SELECT emp_id,
MIN(date) AS StartDate,
MAX(date) AS EndDate
FROM (
SELECT E.emp_id,
C.date,
C.date - ROW_NUMBER() OVER(PARTITION BY E.emp_id
ORDER BY C.date) AS RangeGroup
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)) AS S
GROUP BY emp_id, RangeGroup;
Plamen Ratchev
http://www.SQLStudio.com
Navigation:
[Reply to this message]
|