Create a date table

Question

Post reply

Create a date table

arun1_m1

SSCommitted

Points: 1949
More actions
November 14, 2013 at 6:30 pm

#395832

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)

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

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

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]

arun1_m1 SSCommitted Points: 1949 More actions · Answer 2

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..

pietlinden SSC Guru Points: 63506 More actions · Answer 3

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 4

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]

arun1_m1 SSCommitted Points: 1949 More actions · Answer 5

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

IdStart_range End_Range

12011-07-13 12:05:12.1232011-09-15 11:00:01.234

22011-09-15 11:00:01.2342013-10-04 02:12:12 345

32013-10-04 02:12:12 3452013-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.

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 6

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]

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 7

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]

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 8

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.

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