Create a date table

  • Hi,

    I have a problem in hand where I need to create a date table between start_range and end_range columns of the parent table. I have used the recursive cte way to generate the same. But, I have used the query hint OPTION(MAXRECURSION 0). Kindly let me know if this is the good way and wont have much impact on the performance.

    Please note the start_range and end_range can vary from start of 2009 to till date.

    ;with cte2 as

    (select c.*

    from [parent_table] as c

    union all

    select Id,DATEADD(DD,1,start_range) as activedate,end_range

    from cte2 c2

    where DATEADD(DD,1,c2.start_range)<= c2.start_range

    )

    select * from cte2

    option(maxrecursion 0)

  • Why can't you create a permanent date table? That would be better then building one on the fly every time.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • The start_range and end_range would vary based on input. May be I am unable to explain but for now creating a physical table would not solve the problem. Sorry about that..

  • If you're creating a stored procedure to put your CTE in, just pass @StartRange DATE, @EndRange DATE...

  • How about some DDL for the parent table and sample data with the expected outcome? That would help. Do you not know the largest date range possible for the query? Otherwise you can use maxrecursion 0, but I would verify your parameters ahead of time.

    Just noticed that your date range is 1/1/09 to present.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Thanks Keith. My input date range table could look something like the below

    Id Start_range End_Range

    1 2011-07-13 12:05:12.123 2011-09-15 11:00:01.234

    2 2011-09-15 11:00:01.234 2013-10-04 02:12:12 345

    3 2013-10-04 02:12:12 345 2013-11-04 02:12:12 345

    And I need to create a date table,based on the id that is provided. Please do also note that this input table also gets created from another query, so basically, the date ranges could vary from few months to years.

    Kindly let me know if there is any better approach other than the one I am following.

  • This is your CTE based query:

    CREATE TABLE DateRange (Id TINYINT, Start_Range DATETIME, End_Range DATETIME)

    INSERT DateRange

    VALUES (1, '2011-07-13 12:05:12.123', '2011-09-15 11:00:01.234')

    ,(2, '2011-09-15 11:00:01.234', '2013-10-04 02:12:12.345')

    ,(3, '2013-10-04 02:12:12.345', '2013-11-04 02:12:12.345')

    ;WITH cte2 (ID, Start_Range, End_Range) AS

    (SELECT ID, c.Start_Range, end_range

    FROM DateRange AS c

    UNION ALL

    SELECT Id,DATEADD(DD,1,start_range) AS activedate,end_range

    FROM cte2 c2

    WHERE DATEADD(DD,1,c2.start_range)<= c2.end_range

    )

    SELECT * FROM cte2 ORDER BY ID

    OPTION(MAXRECURSION 0)



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Here is a version using a static Date table (I create and populate it with a CTE, but it is static):

    create table DateTable (DateVal datetime)

    declare @StartDate DateTime,

    @EndDate DateTime

    select @StartDate = '20090101'

    , @EndDate = '20131231';

    WITH DateRange(Date) AS

    (

    SELECT

    @StartDate Date

    UNION ALL

    SELECT

    DATEADD(day, 1, Date) Date

    FROM

    DateRange

    WHERE

    Date < @EndDate

    )

    insert DateTable

    SELECT Date

    FROM DateRange

    --You could remove Maximum Recursion level constraint by specifying a MaxRecusion of zero

    OPTION (MaxRecursion 10000);

    select

    dr.ID

    , dt.DateVal as ActiveDate

    , dr.End_Range

    from DateRange dr

    cross apply DateTable dt

    where dt.DateVal >= cast(dr.Start_Range as date)

    and dt.DateVal < cast(dr.End_Range as date)

    order by dr.ID, dr.Start_Range

    As you can see they return the same results without having to create the CTE every time.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • arun1_m1 (11/14/2013)


    The start_range and end_range would vary based on input. May be I am unable to explain but for now creating a physical table would not solve the problem. Sorry about that..

    Will the range span more than 30 years?

    Do you want the time to be stripped off of the result?

    Are you sure you want possibly duplicated dates caused by the range overlap of the 3 rows or do you just want all the dates from the minimum to the maximum date that covers all rows?

    And to answer your question, recursive CTE's that count are pretty bad for performance and recource usage even for small numbers of rows. Please see the following article for proof of that.

    http://www.sqlservercentral.com/articles/T-SQL/74118/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply