Fill table with dates based on a source date columns

19 Aug 2006 6:14 PM

robc390

I am trying to build a lookup table of valid dates for given business
periods of time.
Given four peices of information:
PERIOD, START_DT, MID_DT, END_DT
I wish to have one row for each date between START_DT, and END_DT for
the PERIOD.
Also, I wish to know the days between START_DT, MID_DT.

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

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

I wish the new table (or output) to be like this:
PERIOD   DATE         DAY_NO
200602    2005-07-05 -89
200602    2005-07-06 -88
200602    2006-07-07 -87
....
200602    2006-10-02 -1
200602    2006-10-03   0
200602    2006-10-04   1
....
200602    2006-12-17   76
TIA
Rob

19 Aug 2006 6:32 PM

AlejandroMH

Hi, you must be use DATEDIFF function.

DATEDIFF
Returns the number of date and time boundaries crossed between two
specified dates.

Syntax
DATEDIFF ( datepart , startdate , enddate )

Arguments
datepart

Is the parameter that specifies on which part of the date to calculate
the difference. The table lists dateparts and abbreviations recognized
by Microsoft® SQL Server™.

Datepart Abbreviations
Year yy, yyyy
quarter qq, q
Month mm, m
dayofyear dy, y
Day dd, d
Week wk, ww
Hour hh
minute mi, n
second ss, s
millisecond ms

Examples
This example determines the difference in days between the current date
and the publication date for titles in the pubs database.

USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO

robc***@hotmail.com wrote:
Show quote

> I am trying to build a lookup table of valid dates for given business
> periods of time.
> Given four peices of information:
> PERIOD, START_DT, MID_DT, END_DT
> I wish to have one row for each date between START_DT, and END_DT for
> the PERIOD.
> Also, I wish to know the days between START_DT, MID_DT.
>
> CREATE TABLE "dbo"."tblDates"
>         ("PERIOD" CHAR(6) NOT NULL,
>         "START_DT" DATETIME NULL,
>         "MID_DT" DATETIME NULL,
>         "END_DT" SMALLINT NOT NULL)
>
> INSERT INTO tblDates VALUES
> ('200505','2005-04-12','2005-04-11','2005-07-05')
> INSERT INTO tblDates VALUES
> ('200602','2005-07-06','2005-07-05','2005-10-03')
> INSERT INTO tblDates VALUES
> ('200603','2005-10-04','2005-10-03','2006-01-17')
> INSERT INTO tblDates VALUES
> ('200604','2006-01-18','2006-01-17','2006-04-10')
> INSERT INTO tblDates VALUES
> ('200605','2006-04-11','2006-04-10','2006-07-04')
> INSERT INTO tblDates VALUES
> ('200702','2006-07-05','2006-07-04','2006-10-02')
>
>
> I wish the new table (or output) to be like this:
> PERIOD   DATE         DAY_NO
> 200602    2005-07-05 -89
> 200602    2005-07-06 -88
> 200602    2006-07-07 -87
> ...
> 200602    2006-10-02 -1
> 200602    2006-10-03   0
> 200602    2006-10-04   1
> ...
> 200602    2006-12-17   76
> TIA
> Rob

19 Aug 2006 11:15 PM

Erland Sommarskog

(robc***@hotmail.com) writes:
Show quote

> I am trying to build a lookup table of valid dates for given business
> periods of time.
> Given four peices of information:
> PERIOD, START_DT, MID_DT, END_DT
> I wish to have one row for each date between START_DT, and END_DT for
> the PERIOD.
> Also, I wish to know the days between START_DT, MID_DT.
>
> CREATE TABLE "dbo"."tblDates"
>         ("PERIOD" CHAR(6) NOT NULL,
>         "START_DT" DATETIME NULL,
>         "MID_DT" DATETIME NULL,
>         "END_DT" SMALLINT NOT NULL)
>
> INSERT INTO tblDates VALUES
> ('200505','2005-04-12','2005-04-11','2005-07-05')
> INSERT INTO tblDates VALUES
> ('200602','2005-07-06','2005-07-05','2005-10-03')
> INSERT INTO tblDates VALUES
> ('200603','2005-10-04','2005-10-03','2006-01-17')
> INSERT INTO tblDates VALUES
> ('200604','2006-01-18','2006-01-17','2006-04-10')
> INSERT INTO tblDates VALUES
> ('200605','2006-04-11','2006-04-10','2006-07-04')
> INSERT INTO tblDates VALUES
> ('200702','2006-07-05','2006-07-04','2006-10-02')
>
>
> I wish the new table (or output) to be like this:
> PERIOD   DATE         DAY_NO
> 200602    2005-07-05 -89
> 200602    2005-07-06 -88
> 200602    2006-07-07 -87
> ...
> 200602    2006-10-02 -1
> 200602    2006-10-03   0
> 200602    2006-10-04   1
> ...
> 200602    2006-12-17   76

This is quite easy to implement if you have a table of numbers. This is
one-column table with numbers from 1 and up. Here is an example to
fill such a table:

   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

I refrain from giving a query for your table, because I don't really
understand the requirement. Why does the result continue to 2006-12-17,
when END_DT is 2005-10-03? What is the purpose of MID_DT? It appears
to always be one day before START_DT?

--
Erland Sommarskog, SQL Server MVP, esq***@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