|
|
Posted by rcamarda on 08/20/06 15:17
Erland,
Sorry about the insert, i see that its returning what I did not expect.
Originally I had the dates quoted, but sql bawked at that. I've been
trying to fix the insert, but after trying cast and convert, it still
wont go.
This gives error about conversion:
INSERT INTO tblDates VALUES ( '200602',cast('2005-07-06' AS
DATETIME),CAST('2005-10-03' AS DATETIME), CAST('2005-12-18' AS
DATETIME))
AH! Finally got this to work:
INSERT INTO tblDates VALUES ('200505' ,convert(datetime,
'2005-04-12'),convert(datetime,'2005-07-05'),
convert(datetime,'2005-09-12' ))
Im still trying to grasp the use of the numbers table. I have a D_Day
table that is the days from 1900 - 2100. Could that be used somehow?
(1900-01-01 has a surrogate key of 1 and 1900-01-02 is 2 and so forth)
You solution works, which I am appreciative of, tho it will take me
working with the code to figure out why :)
Thanks for teaching me something new!
Rob
Erland Sommarskog wrote:
> 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
[Back to original message]
|