Calendar Table in SQL Server

Question

I am trying to create a Calendar Table with following columns and requirements. I have this calendar table made previously and I am not able to update it to my current requirements.

1. Date
2. Week No
3. Week Name
4. Week Start Date
5. Week End Date
6. Month
7. Month Start Date
8. Month End Date
9. List item
10. Quarter
11. Quarter Start Date
12. Quarter End Date
13. Year

Requirements

• --Week Starts from Sunday and ends at Saturday
• --Week Name should be in format 31Aug--to--06Sep
• --Month should be ending date of Week's Month
• --Calendar should start from 2015-08-31
• --Financial Year should be in format of FY16-17
• --Year should start from 1-Jun Till 31 May
• --Quarter should be of 3 months of above mentioned dates
• --Week No should be incremental number should not get reset after year completion.

The Query

DECLARE @StartDate  date = '20150831'
DECLARE @CutoffDate date = '20300101'

;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS      /*SOURCE TABLE WITH OBJECT DEFINITION*/
(
  SELECT
    TheDate         = CONVERT(date, d),
    TheDay          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    TheWeek         = DATEPART(WEEK,      d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    TheMonth        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    TheQuarter      = Concat('Q',DATEPART(Quarter,   d)),
    Financial_Year  = DATEPART(YEAR,      d),
    Financial_Quarter=Datepart(QUARTER,d),
    TheYear         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheFirstOfFYear   = DATEFROMPARTS(YEAR(d), 4, 1),
    TheFirstOfYear   = DATEFROMPARTS(YEAR(d), 1, 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
),
Dimension AS
(
  SELECT
    TheDate, 
    TheDay,
    TheDayName,
    TheDayOfWeek,
   -- TheDayOfWeekInMonth = CONVERT(tinyint, ROW_NUMBER() OVER 
    --                        (PARTITION BY TheFirstOfMonth, TheDayOfWeek ORDER BY TheDate)),
    TheDayOfYear,
    TheWeek,
    TheFirstOfWeek      = DATEADD(DAY, 1 - TheDayOfWeek, TheDate),
    TheLastOfWeek       = DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate)),
    
    TheMonth,
    TheMonthName,
    TheFirstOfMonth,
    TheLastOfMonth      = MAX(TheDate) OVER (PARTITION BY TheYear, TheMonth),
    TheFirstOfNextMonth = DATEADD(MONTH, 1, TheFirstOfMonth),
    TheLastOfNextMonth  = DATEADD(DAY, -1, DATEADD(MONTH, 2, TheFirstOfMonth)),
    TheQuarter,
    TheFirstOfQuarter   = MIN(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheLastOfQuarter    = MAX(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
    TheYear,
    TheFirstOfYear      = DATEFROMPARTS(TheYear, 1,  1),
    TheFirstOfFYear     = DATEFROMPARTS(TheYear, 4,  1),
    TheLastOfYear,
    MMYYYY              = CONVERT(char(2), CONVERT(char(8), TheDate, 101))
                          + CONVERT(char(4), TheYear),
                            

    Financial_Quarter   = Datepart(Quarter,DATEADD(MONTH, -3, TheFirstOfMonth)), /*Starting Financial Quarter from April*/
    Financial_Year      =CASE
                            WHEN Financial_Quarter = 1 THEN DATEPART(Year,Dateadd(Year,-1,TheFirstofYear)) ELSE THEYEAR END
  FROM src
)
SELECT * FROM Dimension
  ORDER BY TheDate
  OPTION (MAXRECURSION 0);

How to convert Months depending on the weeks for example a month starts on Sunday (week also starts from Sunday) date be 01-MM-YYYY and the month should always end on Saturday giving 4 weeks normally in a month. The month cannot start or end with dates of previous week or month it should always have only the whole weeks, starting from Sunday and ending on Saturday.

Answer 1

As I mention in the comments, seems you just need a windowed COUNT. This is a guess, based on a lack of expected results, but this should get you on the right path. I also use the same set based method I used for your colleague's question:

DECLARE @StartDate  date = '20150831'
DECLARE @CutoffDate date = '20300101';
/*
; is a terminator, not a "beginingator". It goes at the end of ALL your statements,
not at the start of statements that require the PREVIOUS statement to be properly terminated.
*/
WITH N AS 
    (SELECT N
     FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),
Tally AS
    (SELECT 0 AS I
     UNION ALL
     SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
     FROM N N1,N N2,N N3, N N4), --Up to 1000 rows. Add more cross joins for more rows
D AS
    (SELECT DATEADD(DAY, T.I, @StartDate) AS d
     FROM Tally T),
Src AS /*SOURCE TABLE WITH OBJECT DEFINITION*/
    (SELECT CONVERT(date, d) AS TheDate,
            DATEPART(DAY, d) AS TheDay,
            DATENAME(WEEKDAY, d) AS TheDayName,
            DATEPART(WEEK, d) AS TheWeek,
            DATEPART(WEEKDAY, d) AS TheDayOfWeek,
            DATEPART(MONTH, d) AS TheMonth,
            DATENAME(MONTH, d) AS TheMonthName,
            CONCAT('Q', DATEPART(QUARTER, d)) AS TheQuarter,
            DATEPART(YEAR, d) AS Financial_Year,
            DATEPART(QUARTER, d) AS Financial_Quarter,
            DATEPART(YEAR, d) AS TheYear,
            DATEFROMPARTS(YEAR(d), MONTH(d), 1) AS TheFirstOfMonth,
            DATEFROMPARTS(YEAR(d), 4, 1) AS TheFirstOfFYear,
            DATEFROMPARTS(YEAR(d), 1, 1) AS TheFirstOfYear,
            DATEFROMPARTS(YEAR(d), 12, 31) AS TheLastOfYear,
            DATEPART(DAYOFYEAR, d) AS TheDayOfYear
     FROM d),
Dimension AS
    (SELECT TheDate,
            TheDay,
            TheDayName,
            TheDayOfWeek,
            CONVERT(tinyint, ROW_NUMBER() OVER (PARTITION BY TheFirstOfMonth, TheDayOfWeek ORDER BY TheDate)) AS TheDayOfWeekInMonth,
            TheDayOfYear,
            TheWeek,
            DATEADD(DAY, 1 - TheDayOfWeek, TheDate) AS TheFirstOfWeek,
            DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate)) AS TheLastOfWeek,
            CONVERT(tinyint, DENSE_RANK() OVER (PARTITION BY TheYear, TheMonth ORDER BY TheWeek)) AS TheWeekOfMonth,
            TheMonth,
            TheMonthName,
            TheFirstOfMonth,
            MAX(TheDate) OVER (PARTITION BY TheYear, TheMonth) AS TheLastOfMonth,
            DATEADD(MONTH, 1, TheFirstOfMonth) AS TheFirstOfNextMonth,
            DATEADD(DAY, -1, DATEADD(MONTH, 2, TheFirstOfMonth)) AS TheLastOfNextMonth,
            TheQuarter,
            MIN(TheDate) OVER (PARTITION BY TheYear, TheQuarter) AS TheFirstOfQuarter,
            MAX(TheDate) OVER (PARTITION BY TheYear, TheQuarter) AS TheLastOfQuarter,
            TheYear,
            DATEFROMPARTS(TheYear, 1, 1) AS TheFirstOfYear,
            DATEFROMPARTS(TheYear, 4, 1) AS TheFirstOfFYear,
            TheLastOfYear,
            CONVERT(char(2), CONVERT(char(8), TheDate, 101)) + CONVERT(char(4), TheYear) AS MMYYYY,
            DATEPART(QUARTER, DATEADD(MONTH, -3, TheFirstOfMonth)) AS Financial_Quarter, /*Starting Financial Quarter from April*/
            CASE
                 WHEN Financial_Quarter = 1 THEN DATEPART(YEAR, DATEADD(YEAR, -1, TheFirstOfYear))
                 ELSE TheYear
            END AS Financial_Year,
            COUNT(CASE WHEN DATENAME(WEEKDAY,TheDate) = 'Sunday' THEN 1 END) OVER (PARTITION BY YEAR(TheDate), MONTH(TheMonth) ORDER BY TheDate) AS TheWeekNo
     FROM src)
SELECT *
FROM Dimension
ORDER BY TheDate;