Hi,
I wish to create new rows of data based on a source table. Example: I
have a file that contains a SESSION (time roughly a calendar quarter
for a University), START_DT, END_DT.
I want to create rows that would be for each session and each day, so
for session 200102 that starts 09/10/2000 and ends 12/15/2000 I want 96
rows, with 200102 for Session, and the 96 days in DAY.

-- Source table:
CREATE TABLE [F___Example_Date] (
[SESSION_ID] [numeric](19, 0) NOT NULL ,
[START_DT] [datetime] NULL ,
[END_DT] [datetime] NULL
) ON [PRIMARY]

-- Sample data :
INSERT INTO [DS_V5_Source].[dbo].[F___Example_Date]([SESSION_ID],
[START_DT], [END_DT])
VALUES(200102, '2000-09-10', '2000-12-15')
INSERT INTO [DS_V5_Source].[dbo].[F___Example_Date]([SESSION_ID],
[START_DT], [END_DT])
VALUES(200103, '2001-01-04', '2001-03-26')

-- Example Target file to be populated:
CREATE TABLE [Target_Date] (
[SESSION_ID] [numeric](19, 0) NOT NULL ,
[Day] [datetime] not null
) ON [PRIMARY]
GO

-- Example of inserts to that represent the desired results (my insert
if for illustration only, not intended to be elegant)
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-10')
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-11')
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-12')
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-13')
-- <... and so forth for all days between 9/10/2000 and 12/15/2000...>
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-12-15')


My need is getting into a new area of my SQL experiance and I'm not
sure how to approach solving this problem. I'm confident once I learn
how to solve this, I will be able to do a lot more with SQL.
TIA
Rob

A common approach is to create a Calendar table to support custom
calendar information. Typically one row per day for the lifetime of
your data then just add columns for the date-sensitive information of
interest:

CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY, session_id INTEGER NOT NULL
DEFAULT 0)

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'21001231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATEDIFF(D,'19991231',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

UPDATE Calendar
SET session_id =
COALESCE(
(SELECT F.session_id
FROM F___Example_Date AS F
WHERE Calendar.caldate BETWEEN F.start_dt AND F.end_dt)
,0)
WHERE EXISTS
(SELECT *
FROM F___Example_Date AS F
WHERE Calendar.caldate BETWEEN F.start_dt AND F.end_dt)

F___Example_Date is now redundant and you can drop it (create a view
instead).

--
David Portas
SQL Server MVP
--

Thanks David,
I cut-n-pasted your example but I am getting an error:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.

Im unfamilar with coalesce, so,. Im not sure what to do.

Let me add more infomation, as my approach might not be ideal:
For any academic quarter (session) we have a count down to the last day
to add or drop a class. This day is known as "Day 0". Because each
session has a different starting date and add drop date, we use this
count down to day zero to compare session to session enrollments, via
Day0. Example, the fall session, the add/drop day might be 9/20/2000
while the next year is 9/23/2001.
I want to create a table that contains the session, a date within that
session and the day number (days from day0) for the given date. I
thought that I could use this table to update my student registrations
with the Day number, since I know the session and day they registered,
as example. I could then find out the number of students from different
sessions where the dayNo <= '0':
SELECT COUNT(*) FROM REGISTRATION WHERE SESSION_ID BETWEEN 200102 AND
200402 AND DAYNO <= 0 GROUP BY SESSION_ID
Thanks
Rob

I think the error message must be caused by duplicate/overlapping time
periods in your table. You didn't specify any keys but I assumed in my
UPDATE that each date would have a unique Session_id.

First, try changing the UPDATE to eliminate exact duplicates:

UPDATE Calendar
SET session_id =
COALESCE(
(SELECT DISTINCT F.session_id
FROM F___Example_Date AS F
WHERE Calendar.caldate BETWEEN F.start_dt AND F.end_dt
AND session_id IS NOT NULL)
,0)
WHERE EXISTS
(SELECT *
FROM F___Example_Date AS F
WHERE Calendar.caldate BETWEEN F.start_dt AND F.end_dt
AND session_id IS NOT NULL)

If you still get an error then you can find any overlapping date ranges
like this:

SELECT T1.*
FROM [F___Example_Date] AS T1,
[F___Example_Date] AS T2
WHERE T1.session_id <> T2.session_id
AND T1.start_dt <= T2.end_dt
AND T1.end_dt >= T2.start_dt

--
David Portas
SQL Server MVP
--