How to split records into a temp table?

Question

How to split records into a temp table?

al_nick

Say Hey Kid

Points: 713
More actions
October 5, 2003 at 9:55 am

#81174

Hi All,
Here's the situation/problem:
Say I have the following "Courses" table with these cols (vcCourseTitle, dtTripStart, dtTripEnd).
EXAMPLE
Intro to SQL | 2003-11-01 | 2003-11-02
--------------------------------------------------
Adv SQL Programming | 2003-11-03 | 2003-11-07
--------------------------------------------------
DTS | 2003-11-02 | 2003-11-05
--------------------------------------------------
SQL Relpication | 2003-11-04 | 2003-11-10
--------------------------------------------------
(total 4 records)
I would like to create a single record in a temp table for EACH DAY the course is being held based
on the table above:
e.g.
Intro to SQL | 2003-11-01
-------------------------------------
Intro to SQL | 2003-11-02
-------------------------------------
Adv SQL Programming | 2003-11-03
-------------------------------------
Adv SQL Programming | 2003-11-04
-------------------------------------
Adv SQL Programming | 2003-11-05
-------------------------------------
Adv SQL Programming | 2003-11-06
-------------------------------------
Adv SQL Programming | 2003-11-07
-------------------------------------
DTS | 2003-11-02
-------------------------------------
DTS | 2003-11-03
-------------------------------------
DTS | 2003-11-04
-------------------------------------
DTS | 2003-11-05
-------------------------------------
SQL Relpication | 2003-11-04
-------------------------------------
SQL Relpication | 2003-11-05
-------------------------------------
SQL Relpication | 2003-11-06
-------------------------------------
SQL Relpication | 2003-11-07
-------------------------------------
SQL Relpication | 2003-11-08
-------------------------------------
SQL Relpication | 2003-11-09
-------------------------------------
SQL Relpication | 2003-11-10
-------------------------------------
(total 18 records)
Obviously I can figure out the duration with DATEDIFF and use DATEADD and add to the first dates,
but can't figure out how to traverse the original table to create "slpit" the records in the first
place.
Any assistance would be GREATLY appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

joelucas Ten Centuries Points: 1078 More actions · Answer 1

First you need to create a table(could be a temp table - based on your requirement). This table(with atleast one field "dates") needs to contain all the dates for the date range. For eg. Temp table #Dates would contain 365 records starting from 01-Jan-2003 to 31-Dec-2003.

Once this is done use the following query.

select a.CourseName, b.Dates from tbl_Course_Schedule a, #dates b where

b.dates between a.startdate and a.enddate

Hope you have the liberty to create this extra table!!!

Thanks

Lucas

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

As long as none of the courses will ever extend more than 255 days, you could use SQL Server's built-in Numbers table:

SELECT c.vcCourseTitle, c.dtTripStart + v.Number DayOffered

FROM Courses c JOIN master..spt_values v ON c.dtTripStart + v.Number <= c.dtTripEnd

WHERE v.Type = 'P'

ORDER By c.vcCourseTitle, DayOffered

--Jonathan

joelucas Ten Centuries Points: 1078 More actions · Answer 3

quote:
As long as none of the courses will ever extend more than 255 days, you could use SQL Server's built-in Numbers table:
SELECT c.vcCourseTitle, c.dtTripStart + v.Number DayOffered
FROM Courses c JOIN master..spt_values v ON c.dtTripStart + v.Number <= c.dtTripEnd
WHERE v.Type = 'P'
ORDER By c.vcCourseTitle, DayOffered
--Jonathan

Hello Jonathan,

This is indeed a smart solution that u have provided. Just a query...how good a practice, is it to refer to a table in Master db from an application. Also it would be nice if you could point me to some material which would describe the tables in the Master DB. To be frank though the query works I don't know what the table spt_values do.

Thanks

Jonathan SSC-Insane Points: 20427 More actions · Answer 4

quote:
Hello Jonathan,
This is indeed a smart solution that u have provided. Just a query...how good a practice, is it to refer to a table in Master db from an application. Also it would be nice if you could point me to some material which would describe the tables in the Master DB. To be frank though the query works I don't know what the table spt_values do.
Thanks

As the spt_values table is not "officially" documented, it may change (and break this query) with future versions of SQL Server. I used it as an example of a Numbers table; in the OP's case, where classes are short, one might also just use a derived table within the query:



SELECT vcCourseTitle, c.dtTripStart + v.Number Dat

FROM Courses c JOIN

(SELECT 0 Number

 UNION ALL SELECT 1

 UNION ALL SELECT 2

 UNION ALL SELECT 3

 UNION ALL SELECT 4

 UNION ALL SELECT 5

 UNION ALL SELECT 6

 UNION ALL SELECT 7

 UNION ALL SELECT 8

 UNION ALL SELECT 9

 UNION ALL SELECT 10) v ON c.dtTripStart + v.Number <= c.dtTripEnd

ORDER By vcCourseTitle, Dat

One could also join to a UDF that returns a numbers table.

The spt_values table is used in many of Microsoft's system stored procedures. Its structure and values should be obvious by just:



SELECT *

FROM master.dbo.spt_values

--Jonathan

Edited by - jonathan on 10/06/2003 06:40:37 AM

--Jonathan

David Burrows SSC Guru Points: 65141 More actions · Answer 5

Personally I would not use spt_values. I would create temp table containg int column containing values from 1 to max number of days between dtTripStart and dtTripEnd and join that table.

Far away is close at hand in the images of elsewhere.
Anon.