Re: Populate table with stored proc
- From: "Plamen Ratchev" <Plamen@xxxxxxxxxxxxx>
- Date: Wed, 25 Apr 2007 03:01:09 GMT
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
.
- References:
- Populate table with stored proc
- From: Nate
- Re: Populate table with stored proc
- From: Plamen Ratchev
- Populate table with stored proc
- Prev by Date: Re: Populate table with stored proc
- Next by Date: Re: Having difficulty setting Back Up to back up file wihout datetime stamp SQL 2K
- Previous by thread: Re: Populate table with stored proc
- Next by thread: Regarding sql error
- Index(es):
Relevant Pages
|
