主页 / dba / 问题 / 268427
Accepted
Kulstad
Asked: 2020-06-04 07:12:14 +0800 CST

根据 SQL Server 2017 中的自定义日期细分日历表

  • 772

我有一个包含以下会计年度定义的表：

FiscalYear     | Period     | PeriodStartDate     | PeriodEndDate
=======================================================================
2020           |     1      | 2019-10-27 00:00:00 | 2019-11-23 00:00:00
2020           |     2      | 2019-11-24 00:00:00 | 2019-12-21 00:00:00
2020           |     3      | 2019-12-22 00:00:00 | 2020-01-18 00:00:00
2020           |     4      | 2020-01-19 00:00:00 | 2020-02-15 00:00:00
2020           |     5      | 2020-02-16 00:00:00 | 2020-03-14 00:00:00
2020           |     6      | 2020-03-15 00:00:00 | 2020-04-11 00:00:00
2020           |     7      | 2020-04-12 00:00:00 | 2020-05-09 00:00:00
2020           |     8      | 2020-05-10 00:00:00 | 2020-06-06 00:00:00
2020           |     9      | 2020-06-07 00:00:00 | 2020-07-04 00:00:00
2020           |     10     | 2020-07-05 00:00:00 | 2020-08-01 00:00:00
2020           |     11     | 2020-08-02 00:00:00 | 2020-08-29 00:00:00
2020           |     12     | 2020-08-30 00:00:00 | 2020-09-26 00:00:00
2020           |     13     | 2020-09-27 00:00:00 | 2020-10-24 00:00:00

在阅读了 Aaron Bertrand 关于如何构建日历表的文章（https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/）之后，我想合并他的专栏，并将我的财务表分解如下：

  • FiscalYear -- 在 FiscalReportingPeriod_tb (FiscalYear)
  • 财政季度
  • FiscalPeriod -- 在 FiscalReportingPeriod_tb (期间)
  • 财政周刊
  • 财政日
  • 季度周
  • 季度日
  • 周期周
  • 期间日
  • FirstOfFiscalMonth -- 在 FiscalReportingPeriod_tb (PeriodStartDate)
  • EndOfFiscalMonth -- 在 FiscalReportingPeriod_tb (PeriodEndDate)
  • 财年开始
  • 财政年度结束
  • 第一财季
  • 财政季度末

由于没有创建客户函数来获得所需的输出，我不确定如何将我的细分与 Aaron 创建他的表的方式结合起来。

编辑：为了帮助解决 Aaron 的问题，如果我从中取出 FiscalQuarter 项目（一开始对我来说从来没有意义），这就是我要找的：

FiscalYear     | FiscalPeriod     | FiscalWeek     | FiscalDay     | PeriodWeek     | PeriodDay     | FirstOfFiscalMonth     | EndOfFiscalMonth     | StartOfFiscalYear     | EndOfFiscalYear     | TheDate
================================================================================================================================================================================================================
2020           |       1          |       1        |      1        |      1         |      1        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-10-27
2020           |       1          |       1        |      2        |      1         |      2        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-10-28
2020           |       1          |       1        |      3        |      1         |      3        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-10-29
2020           |       1          |       1        |      4        |      1         |      4        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-10-30
2020           |       1          |       1        |      5        |      1         |      5        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-10-31
2020           |       1          |       1        |      6        |      1         |      6        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-01
2020           |       1          |       1        |      7        |      1         |      7        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-02
2020           |       1          |       2        |      8        |      2         |      1        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-03
2020           |       1          |       2        |      9        |      2         |      2        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-04
2020           |       1          |       2        |      10       |      2         |      3        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-05
2020           |       1          |       2        |      11       |      2         |      4        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-06
2020           |       1          |       2        |      12       |      2         |      5        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-07
2020           |       1          |       2        |      13       |      2         |      6        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-08
2020           |       1          |       2        |      14       |      2         |      7        |     2019-10-27         |    2019-11-23        |     2019-10-27        |   2020-10-24        |   2019-11-09
sql-server t-sql

1 个回答

  1. Best Answer

    这将构建您要查找的表：

    WITH
ImportantDates AS (
    SELECT * FROM (
        VALUES
            /*
                This approach assumes Important Dates are continous
                A better approach would be to build from start dates
                to ensure that it s.
            */
            (2020,1 ,'2019-10-27','2019-11-23')
            ,(2020,2 ,'2019-11-24','2019-12-21')
            ,(2020,3 ,'2019-12-22','2020-01-18')
            ,(2020,4 ,'2020-01-19','2020-02-15')
            ,(2020,5 ,'2020-02-16','2020-03-14')
            ,(2020,6 ,'2020-03-15','2020-04-11')
            ,(2020,7 ,'2020-04-12','2020-05-09')
            ,(2020,8 ,'2020-05-10','2020-06-06')
            ,(2020,9 ,'2020-06-07','2020-07-04')
            ,(2020,10,'2020-07-05','2020-08-01')
            ,(2020,11,'2020-08-02','2020-08-29')
            ,(2020,12,'2020-08-30','2020-09-26')
            ,(2020,13,'2020-09-27','2020-10-24')
    ) V(FiscalYear,Period,PeriodStartDate,PeriodEndDate)
),
Days as (
    SELECT 
        TOP (
            SELECT DATEDIFF(D, MIN(PeriodStartDate), MAX(PeriodEndDate)) 
            FROM ImportantDates
        ) -- How high we want to count
        DATEADD(
            D, 
            ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1, -- ROW_NUMBER is 1 Based, so subtract 1
            (SELECT MIN(PeriodStartDate) FROM ImportantDates)
        ) AS TheDate
    FROM
        sys.columns a CROSS JOIN sys.columns b -- something with a lot of rows

),
Segement AS (
    SELECT
        FiscalYear
        ,Period FiscalPeriod
        ,DATEDIFF(WEEK, MIN(TheDate) OVER (PARTITION BY FiscalYear), TheDate) + 1 FiscalWeek 
        ,DATEDIFF(DAY, MIN(TheDate) OVER (PARTITION BY FiscalYear), TheDate ) + 1 FiscalDay
        ,DATEDIFF(WEEK, MIN(TheDate) OVER (PARTITION BY FiscalYear, Period), TheDate) + 1 PeriodWeek
        ,DATEDIFF(DAY, MIN(TheDate) OVER (PARTITION BY FiscalYear, Period), TheDate ) + 1 PeriodDay
        ,MIN(TheDate) OVER (PARTITION BY FiscalYear, Period) FirstOfFiscalMonth
        ,MAX(TheDate) OVER (PARTITION BY FiscalYear, Period) EndOfFiscalMonth
        ,MIN(TheDate) OVER (PARTITION BY FiscalYear) StartOfFiscalYear
        ,MAX(TheDate) OVER (PARTITION BY FiscalYear) EndOfFiscalYear
        ,TheDate
    FROM
        Days JOIN ImportantDates ON TheDate BETWEEN PeriodStartDate AND PeriodEndDate

)
SELECT * FROM Segement
    • 2

相关问题