I am looking to populate a Schedule table with information from two
other tables. I am able to populate it row by row, but I have created
tables that should provide all necessary information for me to be
able
to automatically populate a "generic" schedule for a few weeks or
more
at a time.

The schedule table contains:
(pk) schedule_id, start_datetime, end_datetime, shift_employee,
shift_position


A DaysOff table contains:
(pk) emp_id, dayoff_1, dayoff_2 <-- the days off are entered in day
of
week (1-7) form


A CalendarDays table contains:
(pk) date, calendar_dow <-- dow contains the day of week number (as
above) for each day until 2010.


My main question is how to put all of this information together and
have SQL populate the rows with data based on days off, for a few
weeks in advance. Any
suggestions?

Nate (nate.borland (AT) westecnow (DOT) com) writes:
The problem looks kind of interesting, but alas the hour is late here,
so I'm not able to compose a solution right now. But I would like some
clarifications:

1) Which version of SQL Server are you using?
2) Do I understand the DaysOff table correctly that this table details
two days in the week a certain employer never works, for instance
one bloke is always free on Tuesdays and Fridays?
3) What is supposed to go into shift_employee and shift_position?

It would be great if you could post:
1) CREATE TABLE statements for your tables.
2) INSERT statements with sample data (at least for the first two tables).
3) The desired result given the sample.

This makes it easy to test a solution. It also helps to clarify your
problem.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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

Just to clarify, you can still get the same results without normalizing the
DaysOff table...

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 DaysOff AS O
WHERE O.emp_id = E.emp_id
AND C.calendar_dow IN (O.dayoff_1, O.dayoff_2));

Plamen Ratchev
http://www.SQLStudio.com

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