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