Creating rows based on date range from another table

Giganews Newsgroups
Subject: Creating rows based on date range from another table
Posted by:  rcamarda (robc3…@hotmail.com)
Date: 19 Aug 2006

I wish to build a table based on values from another table.
I need to populate a table between two dates from another table. Using
the START_DT and END_DT, create records between those dates.
I need a new column that is the days between the date and the MID_DT
The data I wish to end with would look something like this:

PERIOD    DATE      DAY_NO
200602    2005-07-06 -89
200602    2005-07-07 -88
200602    2005-07-08 -87
<...>
200602    2005-10-02  -2
200602    2005-10-03  -1
200602    2005-10-04  0
200602    2005-10-05  1
<...>
200602    2005-12-18  75

CREATE TABLE "dbo"."tblDates"
        ("PERIOD" CHAR(6) NOT NULL,
        "START_DT" DATETIME NULL,
        "MID_DT" DATETIME NULL,
        "END_DT" DATETIME NOT NULL)

INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)
INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)
INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)
INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)
INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)
INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

Replies