Sql-Server
根據 SQL Server 2017 中的自定義日期細分日曆表
我有一個包含以下會計年度定義的表：
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
這將建構您要查找的表：
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