Search This Blog & Web

Thursday, May 3, 2012

CTE to return Date Calender in SQL SERVER 2008

One of my Client asked me to return month calender and return data from another table. But that table does not have data against whole month. There is only selected dates that have value. I have used following technique to get this in SQL SERVER 2008 and above. If you want to use if below SQL SERVER 2008 then you need to change Date data type to datetime and convert your date field at join. To convert look at this post
http://shamas-saeed.blogspot.com/2009/05/convert-date-time-function-in-sql.html
You can also create a function that will return Daily, Monthly, Quarterly and Yearly date range results. Have a look at this link.
http://shamas-saeed.blogspot.com/2011/05/returning-date-range-from-function-day.html


Declare @tbl Table(ID int identity(1,1), [Date] date,val int)

Insert into @tbl ([Date],val) values
('2012-04-27',30),
('2012-05-02',2),
('2012-04-10',10),
('2012-04-10',40),
('2012-04-27',723)

;WITH Dates AS (
SELECT
[Date] =convert(date,getdate(),103)
UNION ALL
SELECT
[Date] = DATEADD(DAY, -1, [Date])
FROM
Dates
WHERE
Date > dateadd(dd,-30,getdate())
)

SELECT
 Dates.Date as [Date],isnull(t.Val,0) as Value
FROM
 Dates left outer join @tbl t on Dates.Date =t.Date


OPTION (MAXRECURSION 31)

Result can be shown in pic. As there are 31 rows i can get print only first few rows but it is showing the required result.


Post a Comment