SQL print year-round calendar

Original: SQL print year-round calendar

Database environment: SQL SERVER 2008R2

I've previously written a SQL for this month's calendar, with detailed instructions. For details, please refer to the previous blog post-generate this month's calendar.

The year-round calendar only adds month information to the month's calendar and is calculated by month group.

Share SQL directly below

/*Base data: Year date, number of days of the year*/ withx0 as(SELECT   CONVERT(DATE,'2015-01-01') asYearbegin,CONVERT(DATE,'2015-12-31') asyearend,DATEDIFF( Day,'2015-01-01','2015-12-31') asdayscount),/*Enumerate all dates throughout the year*/X1 as(SELECT   DATEADD( Day, Number, Yearbegin) asndate fromx0, master.dbo.spt_values SPTWHERESpt.type= 'P'                         andSpt. Number >= 0                         andSpt. Number <=dayscount),/*List the month of the year , the week of the week, the day of the month*/X2 as(SELECTNdate,DATEPART(Month, ndate) asNmonth,DATEPART(Week, Ndate) asNweek,DATEPART(Weekday, Ndate) asNweekday,DATEPART( Day, ndate) asNday fromx1),/*GROUP by month and week to generate year-round calendars*/X3 as(SELECTNmonth, Nweek,ISNULL(CAST(MAX( CaseNweekday when 1  ThenNdayEND) as VARCHAR(2)),"') asDay,ISNULL(CAST(MAX( CaseNweekday when 2  ThenNdayEND) as VARCHAR(2)),"') asOne,ISNULL(CAST(MAX( CaseNweekday when 3  ThenNdayEND) as VARCHAR(2)),"') asTwo,ISNULL(CAST(MAX( CaseNweekday when 4  ThenNdayEND) as VARCHAR(2)),"') asThree,ISNULL(CAST(MAX( CaseNweekday when 5  ThenNdayEND) as VARCHAR(2)),"') asFour,ISNULL(CAST(MAX( CaseNweekday when 6  ThenNdayEND) as VARCHAR(2)),"') asFive,ISNULL(CAST(MAX( CaseNweekday when 7  ThenNdayEND) as VARCHAR(2)),"') asSix fromX2GROUP  byNmonth, Nweek)/*The same value for the month is displayed only in the first row*/    SELECT  REPLACE( Case  whenRow_number () Over(PARTITION byNmonthORDER  byNweek)= 1                          ThenNmonthELSE -1                    END,-1,"') asmonth, day, one, two, three, four, five, six fromX3

Code is not too much, more than 60 lines, but also understand. If you feel that putting the "Sunday" in the first column is a bit awkward, you can generate postmenstrual on Sunday in X2.

Do something special to deal with it.

Paste the results.

(End of this article)

SQL print year-round calendar