创建不带递归的日历表

Question

我想在不使用递归的情况下创建一个日历表，就像我之前准备的那样。我如何才能完成这项任务。下面的代码中提到了所有必需的列，代码注释中只提到了很少的其他细节。..................................................................................................................................................................................................................................................

DECLARE @StartDate  date = '20200101'
DECLARE @CutoffDate date = GETDATE()

;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)),
    TheWeekOfMonth      = CONVERT(tinyint, DENSE_RANK() OVER 
                            (PARTITION BY TheYear, TheMonth ORDER BY TheWeek)),
    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);

Answer 1

正如我在评论中提到的，使用计数。它们比rCTE快得多，因为它们不是递归的。我在这里使用内联计数：

DECLARE @StartDate date = '20200101';
DECLARE @CutoffDate date = GETDATE();
/*
; 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), --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
     FROM src)
SELECT *
FROM Dimension
ORDER BY TheDate;