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

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

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

1. excelent, thank you for compartir

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

@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)
FROM cteTally