Posted by Erland Sommarskog on 04/24/07 22:20

Nate (nate.borland@westecnow.com) writes:
> 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?

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@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]