Populating A Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Populating A Table

Author

Topic

noelskiz
Starting Member

1 Post

Posted - 2004-09-16 : 22:46:01

Hi all! I need to create a SP that will populate a table based on a least three parameters.
Say, schedule (int): 1, startdate (datetime) : 9/1/2004 & enddate (datetime) : 9/3/2004

It should then look at my reference tables of employees and schedule such that the result would be a new set of record to the work table, something like this:

NAME WORKDATE
Mr. A 9/1/04
Mr. A 9/2/04
Mr. A 9/3/04
Mr. B 9/1/04
Mr. B 9/2/04
Mr. B 9/3/04

For very employee with the schedule of 1 will be added to the work table as many times as the workdates, in this case 3 times (9/1/04 to 9/3/04).

Thank you.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-09-17 : 03:57:52

With a table of numbers this is quite easy to do.
I will provide you with a technical example and I think You can work out the rest.
In the example I create a number table with nbrs from 0 - 99, you will likely want a longer one.
Also in the code I use the fact that 0 is the first nbr in the number table.

SELECT t1.n + t2.n*10 AS nr INTO #tally FROM
( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t1
CROSS JOIN
( SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 ) t2
ORDER BY 1

DECLARE @startdate DATETIME, @enddate DATETIME
SELECT @startdate = '9/1/2004', @enddate = '9/3/2004'

SELECT CAST( @startdate + t.nr AS DATETIME ) AS MyWorkDate
FROM #tally t
WHERE t.nr <= DATEDIFF(DAY,@startdate,@enddate)

DROP TABLE #tally

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources