Alternative method of querying the calendar using the CTE method

I have also developed a solution of mine, but using simple t-sql CTE instead of t-sql PIVOT statements. Both methods are actually ending with the same result set.
Here is the sample t-sql code for the t-sql calendar listing.
WITH CTE AS
(
SELECT
wd = DATEPART(WEEK, [date]),
[date],
Sunday = CASE WHEN [days] = 'Sunday' THEN [date] ELSE NULL END,
Monday = CASE WHEN [days] = 'Monday' THEN [date] ELSE NULL END,
Tuesday = CASE WHEN [days] = 'Tuesday' THEN [date] ELSE NULL END,
Wednesday = CASE WHEN [days] = 'Wednesday' THEN [date] ELSE NULL END,
Thursday = CASE WHEN [days] = 'Thursday' THEN [date] ELSE NULL END,
Friday = CASE WHEN [days] = 'Friday' THEN [date] ELSE NULL END,
Saturday = CASE WHEN [days] = 'Saturday' THEN [date] ELSE NULL END
FROM dbo.getFullmonth ('20080229')
)
SELECT
MAX(Sunday) Sunday,
MAX(Monday) Monday,
MAX(Tuesday) Tuesday,
MAX(Wednesday) Wednesday,
MAX(Thursday) Thursday,
MAX(Friday) Friday,
MAX(Saturday) Saturday
FROM CTE
GROUP BY wd
Here is the CTE output for generating sql calendar dates on a MS SQL Server 2005 or later (SQL Server 2008).
t-sql cte command to format dates as calendar

Popular posts from this blog

Using SSRS web services to render a report as a PDF

Integrating MIGS eGate Payment gateway using C# for Oz businesses (Server-Hosted Payment method)

Creating a Link Server to MYSQL from MSSQL