locked
'Exploding' datetime and duration RRS feed

  • Question

  • Hi Everyone,

    Is there a simple way that given a table with a datetime column and the number of days, I can 'explode' this to give a complete list of days. For example:

    DateTime          Duration
    01/01/09           2
    01/02/09           3

    Becomes:

    01/01/09
    02/01/09
    01/02/09
    02/02/09
    03/02/09

    (All dates in DDMMYY format)

    Any help would be much appreciated.

    Thanks!

    Matt
    Thursday, April 16, 2009 2:59 PM

Answers

  • You can solve this having a table of numbers.


    CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
    RETURN
      WITH
      L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
      L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
      L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
      L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
      L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
      L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
      Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
      SELECT n FROM Nums WHERE n <= @n; 
    GO

    CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
    GO

    INSERT Numbers(Number) SELECT n FROM fn_nums(1000000);
    GO

    select dateadd([day], Number - 1, [datetime]) as dt
    from t inner join Numbers as n on n.Number <= t.duration
    order by dt
    GO


    > (All dates in DDMMYY format)

    What is the data type of [datetime]?


    AMB
    Thursday, April 16, 2009 3:12 PM