Server构建动态日历表

Question

我需要创建一个具有以下条件的财政日历表：

• 财政年度开始:2019-03-26财政年度结束: 2020-03-25

这些月的期限为下个月的26至25日。例如，下面是该结构的要点：

| Fiscal Year | Month Number | Month Start Date | Month End Date |
|-------------|--------------|------------------|----------------|
| 2019        | 1            | 2019-03-26       | 2019-04-25     |
| 2019        | 2            | 2019-04-26       | 2019-05-25     |
| 2019        | 3            | 2019-05-26       | 2019-06-25     |
| 2019        | 4            | 2019-06-26       | 2019-07-25     |
| 2019        | 5            | 2019-07-26       | 2019-08-25     |
| 2019        | 6            | 2019-08-26       | 2019-09-25     |
| 2019        | 7            | 2019-09-26       | 2019-10-25     |
| 2019        | 8            | 2019-10-26       | 2019-11-25     |
| 2019        | 9            | 2019-11-26       | 2019-12-25     |
| 2019        | 10           | 2019-12-26       | 2020-01-25     |
| 2019        | 11           | 2020-01-26       | 2020-02-25     |
| 2019        | 12           | 2020-02-26       | 2020-03-25     |

这将需要一个滚动的日历，以增加更多的年向前。

Answer 1

您可以使用类似于下面的查询来填充物理表以获得更好的性能。

DECLARE @StartDate DATETIME = '01/01/2015' 
DECLARE @EndDate DATETIME = '12/01/2016' 

    ;WITH OrderedDays as 
    ( 
        SELECT CalendarDate = @StartDate
        UNION ALL 
        SELECT CalendarDate = DATEADD(DAY, 1, CalendarDate)
        FROM OrderedDays WHERE DATEADD (DAY, 1, CalendarDate) <= @EndDate 
    ),
    Calendar AS
    (
        SELECT
            DayIndex = ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY CalendarDate),
            CalendarDate,
            CalenderDayOfMonth = DATEPART(DAY, CalendarDate),
            CalenderMonthOfYear = DATEPART(MONTH, CalendarDate),
            CalendarYear = DATEPART(YEAR, CalendarDate),
            CalenderWeekOfYear = DATEPART(WEEK, CalendarDate),
            CalenderQuarterOfYear = DATEPART(QUARTER, CalendarDate),
            CalenderDayOfYear =  DATEPART(DAYOFYEAR, CalendarDate),
            CalenderDayOfWeek =  DATEPART(WEEKDAY, CalendarDate),
            CalenderWeekday =  DATENAME(WEEKDAY, CalendarDate),
            StartOfMonth =  DATEADD(MONTH, DATEDIFF(MONTH, 0, CalendarDate), 0),
            EndOfMonth = DATEADD (DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, CalendarDate) + 1, 0))
        FROM
            OrderedDays                 
    )
    SELECT 
        FiscalYear = CalendarYear,
        MonthNumber = CalenderMonthOfYear,
        MonthStartDate = MIN(StartOfMonth),
        MonthEndDate = MIN(EndOfMonth) 
    FROM 
        Calendar
    GROUP BY 
        CalendarYear,
        CalenderMonthOfYear
    ORDER BY
        CalendarYear,
        CalenderMonthOfYear

    OPTION (MAXRECURSION 0) 

基于来自Larnu的反馈，下面是一个版本，它使用的是.。

DECLARE @StartDate DATETIME = '01/01/2019'
DECLARE @NumberOfDays INT = 10000

;WITH R1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    R2(N) AS (SELECT 1 FROM R1 a, R1 b),
    R3(N) AS (SELECT 1 FROM R2 a, R2 b), 
    Tally(Number) AS (SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM R3)

,WithTally AS   
(
    SELECT CalendarDate = DATEADD(DAY,T.Number,@StartDate)
    FROM
        Tally T
    WHERE
        T.Number < @NumberOfDays
)   


,Calendar AS
(
    SELECT
        DayIndex = ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY CalendarDate),
        CalendarDate,
        CalenderDayOfMonth = DATEPART(DAY, CalendarDate),
        CalenderMonthOfYear = DATEPART(MONTH, CalendarDate),
        CalendarYear = DATEPART(YEAR, CalendarDate),
        CalenderWeekOfYear = DATEPART(WEEK, CalendarDate),
        CalenderQuarterOfYear = DATEPART(QUARTER, CalendarDate),
        CalenderDayOfYear =  DATEPART(DAYOFYEAR, CalendarDate),
        CalenderDayOfWeek =  DATEPART(WEEKDAY, CalendarDate),
        CalenderWeekday =  DATENAME(WEEKDAY, CalendarDate),
        StartOfMonth =  DATEADD(MONTH, DATEDIFF(MONTH, 0, CalendarDate), 0),
        EndOfMonth = DATEADD (DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, CalendarDate) + 1, 0))
    FROM
        WithTally                   
)
SELECT 
    FiscalYear = CalendarYear,
    MonthNumber = CalenderMonthOfYear,
    MonthStartDate = MIN(StartOfMonth),
    MonthEndDate = MIN(EndOfMonth) 
FROM 
    Calendar
GROUP BY 
    CalendarYear,
    CalenderMonthOfYear
ORDER BY
    CalendarYear,
    CalenderMonthOfYear

OPTION (MAXRECURSION 0)