|
|
Posted by Erland Sommarskog on 08/20/06 12:38
rcamarda (robc390@hotmail.com) writes:
> 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)
Thanks for posting table definition and data. However, I would appreciate
if you also tested your repro script before you post. I was puzzled not
getting any rows back first from my query, but then I realised that
2005-04-12 > 2005-09-12. (Run the above folliwed by a SELECT on the
table to see why.)
Anyway, as I said in another newsgroup, you need a table of numbers. Here
is a way to create such a table with a million numbers:
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
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 UNION
SELECT 0)
INSERT Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number > 0
Given this table, we can write this query:
SELECT d.PERIOD, dateadd(DAY, n.Number - 1, d.START_DT),
datediff(DAY, d.MID_DT, dateadd(DAY, n.Number - 1, d.START_DT))
FROM tblDates d
CROSS JOIN Numbers n
WHERE dateadd(DAY, n.Number - 1, d.START_DT)
BETWEEN d.START_DT AND d.END_DT
ORDER BY d.PERIOD, 2
--
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
Navigation:
[Reply to this message]
|