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
|
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
|
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.
excelent, thank you for compartir
ReplyDeleteUsing 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 .
ReplyDeleteDECLARE @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