SQL Server Performance

Create dynamic date resultset for joining

Discussion in 'Contribute Your SQL Server Scripts' started by Chappy, Mar 6, 2004.

  1. Chappy New Member


    /* ------------------------------------------------
    Function: dbo.GetDateRange
    Author: Paul Chapman (chappi (dot) uk (at) virgin (dot) net)
    Date: 05/03/2004
    Purpose: Dynamically generate a resultset of dates for a given range/increment
    ------------------------------------------------ */
    CREATE FUNCTION dbo.GetDateRange(
    @startdate datetime,
    @enddate datetime,
    @increment int = 1)
    RETURNS @DateRange TABLE (datevalue datetime)
    AS
    BEGIN
    DECLARE @loopdate datetime
    SELECT @loopdate = @startdate
    WHILE (@loopdate <= @enddate)
    BEGIN
    INSERT INTO @DateRange VALUES (@loopdate)
    SELECT @loopdate = DATEADD(d, @increment, @loopdate)
    END
    RETURN
    END

    Example



    DECLARE @startdate datetime, @enddate datetime
    SELECT @startdate = GETDATE()
    SELECT @enddate = DATEADD(yyyy, 1, @startdate)
    SELECT dateValue, "Day_Delta"=DATEDIFF(dd, GETDATE(), dateValue) from dbo.GetDateRange(@startDate, @endDate, 7)

    dateValue Day_Delta
    ------------------------------------------------------ -----------
    2004-03-06 12:18:36.577 0
    2004-03-13 12:18:36.577 7
    2004-03-20 12:18:36.577 14
    2004-03-27 12:18:36.577 21
    2004-04-03 12:18:36.577 28
    2004-04-10 12:18:36.577 35
    2004-04-17 12:18:36.577 42
    2004-04-24 12:18:36.577 49
    2004-05-01 12:18:36.577 56
    2004-05-08 12:18:36.577 63
    2004-05-15 12:18:36.577 70
    2004-05-22 12:18:36.577 77
    2004-05-29 12:18:36.577 84
    2004-06-05 12:18:36.577 91
    2004-06-12 12:18:36.577 98
    2004-06-19 12:18:36.577 105
    2004-06-26 12:18:36.577 112
    2004-07-03 12:18:36.577 119
    2004-07-10 12:18:36.577 126
    2004-07-17 12:18:36.577 133
    2004-07-24 12:18:36.577 140
    2004-07-31 12:18:36.577 147
    2004-08-07 12:18:36.577 154
    2004-08-14 12:18:36.577 161
    2004-08-21 12:18:36.577 168
    2004-08-28 12:18:36.577 175
    2004-09-04 12:18:36.577 182
    2004-09-11 12:18:36.577 189
    2004-09-18 12:18:36.577 196
    2004-09-25 12:18:36.577 203
    2004-10-02 12:18:36.577 210
    2004-10-09 12:18:36.577 217
    2004-10-16 12:18:36.577 224
    2004-10-23 12:18:36.577 231
    2004-10-30 12:18:36.577 238
    2004-11-06 12:18:36.577 245
    2004-11-13 12:18:36.577 252
    2004-11-20 12:18:36.577 259
    2004-11-27 12:18:36.577 266
    2004-12-04 12:18:36.577 273
    2004-12-11 12:18:36.577 280
    2004-12-18 12:18:36.577 287
    2004-12-25 12:18:36.577 294
    2005-01-01 12:18:36.577 301
    2005-01-08 12:18:36.577 308
    2005-01-15 12:18:36.577 315
    2005-01-22 12:18:36.577 322
    2005-01-29 12:18:36.577 329
    2005-02-05 12:18:36.577 336
    2005-02-12 12:18:36.577 343
    2005-02-19 12:18:36.577 350
    2005-02-26 12:18:36.577 357
    2005-03-05 12:18:36.577 364

    (53 row(s) affected)
  2. thomas New Member

    thanks, that could be very handy.

    Tom Pullen
    DBA, Oxfam GB

Share This Page