Sunday, November 29, 2015

SQL – Recursive Date with CTE

In the daily analytical process, we need to verify the daily prices for the particular stack. It’s become very difficult if we need to get the last two or more year price performance because we never know that when price is missing. To get the price missing date, we need to create the mirror calendar date for that date range. With the help of this mirror date calendar, we can easily find out the price missing date. To generate the Calendar Date, CTE (Common Table Expression) would be very useful due to its recursive nature.


To get the recursive date, we need two date parameters such as start date to end date where start date will introduced the starting date of the calendar and end date will introduced the last date of the calendar as given below:

--- Input parameters
--- Start date of the calendar
DECLARE @inpStartDate DATE=DATEADD(YY,-2,GETDATE()),

--- End Date of the calendar
@inpEndDate DATE=GETDATE()

SELECT StartDate=@inpStartDate, EndDate=@inpEndDate

StartDate
EndDate
11/29/2013
11/29/2015


In our example, we are going to get the calendar date having DayDate, NameOfDay, NameOfMonth, YearOf as given below:

--- Input parameters
--- Start date of the calendar
DECLARE @inpStartDate DATE=DATEADD(YY,-2,GETDATE()),

--- End Date of the calendar
@inpEndDate DATE=GETDATE()

SELECT StartDate=@inpStartDate, EndDate=@inpEndDate

---- Common Table Expression
;WITH GetCalander (DayDate, NameOfDay, NameOfMonth, YearOf)
AS
(
---- Block 1
---- This block will create the base of table to generate the
---- First record of the calendar
SELECT DayDate=@inpStartDate,
NameOfDay=DateName(DW,@inpStartDate),
NameOfMonth=DateName(M,@inpStartDate),
YearOf=YEAR(@inpStartDate)

UNION ALL
---- Block 2
---- This block will use the first block and
---- will add the 1 day in the current date of the GetCalander
---- This will produce a resultset till the end of the @inpEndDate
SELECT DayDate=DateADD(D,1,DayDate),
NameOfDay=DateName(DW,DateADD(D,1,DayDate)),
NameOfMonth=DateName(M,DateADD(D,1,DayDate)),
YearOf=YEAR(DateADD(D,1,DayDate))
FROM GetCalander
WHERE DayDate<=@inpEndDate
)

---- Pull the calendar data
SELECT * FROM GetCalander
---- Set to get all data
OPTION (MAXRECURSION 0)

---- Calendar should have the folloing data till the end of the end date parameter

DayDate
NameOfDay
NameOfMonth
YearOf
11/29/2013
Friday
November
2013
11/30/2013
Saturday
November
2013
12/1/2013
Sunday
December
2013
12/2/2013
Monday
December
2013
12/3/2013
Tuesday
December
2013
12/4/2013
Wednesday
December
2013
12/5/2013
Thursday
December
2013
12/6/2013
Friday
December
2013
12/7/2013
Saturday
December
2013
12/8/2013
Sunday
December
2013
12/9/2013
Monday
December
2013
12/10/2013
Tuesday
December
2013
12/11/2013
Wednesday
December
2013
12/12/2013
Thursday
December
2013
12/13/2013
Friday
December
2013
12/14/2013
Saturday
December
2013
12/15/2013
Sunday
December
2013

So, CTE is the best way to get the this kind of the data based on the some condition where we need to recursive functionality.

2 comments:

  1. Using a CTE to do this is REALLY REALLY inefficient!! Your code takes 6600 IO and 16 cpu ticks to perform the calculation for 2 years of rows (reported in profiler)!! A simple numbers table will do the same with ZERO IO and ZERO cpu ticks .

    DECLARE @s DATE=DATEADD(YY,-2,GETDATE()),
    @e DATE=GETDATE()
    SELECT StartDate=@s, EndDate=@e

    --inline Numbers Table
    ; WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b),
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10000 max
    cteTally(N) AS (SELECT 0 UNION ALL SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)
    SELECT DayDate=DateADD(D,N,@s),
    NameOfDay=DateName(DW,DateADD(D,N,@s)),
    NameOfMonth=DateName(M,DateADD(D,N,@s)),
    YearOf=YEAR(DateADD(D,N,@s))
    FROM cteTally
    WHERE DateADD(D,N,@s)<=@e

    ReplyDelete

Popular Posts