在 SQL 中的時間範圍之間獲取記錄的替代方法（計算財政年度） (Alternative ways to get records between a time frame in SQL (calculate fiscal year))

問題描述

在 SQL 中的時間範圍之間獲取記錄的替代方法（計算財政年度） (Alternative ways to get records between a time frame in SQL (calculate fiscal year))

我有這個表，我寫了，創建表查詢和示例也是下面的數據：

DECLARE @TCS_DIVIDEND_PER_YEAR TABLE
(RECORD_DATE DATE,
DIVIDENDPERSHARE INT)

INSERT INTO @TCS_DIVIDEND_PER_YEAR values
('07/16/20',5)
,('06/03/20', 6)
,('03/19/20', 12)
,('01/23/20', 5)
,('10/17/19', 5)
,('10/17/19', 40)
,('07/16/19', 5)
,('06/04/19', 18)
,('01/17/19', 4)
,('10/23/18', 4)
,('07/17/18', 4)
,('05/31/18', 29)
,('01/22/18', 7)
,('10/25/17', 7)
,('07/24/17', 7)
,('06/13/17', 27.5)
,('01/23/17', 6.5)
,('10/24/16', 6.5)
,('07/25/16', 6.5)
,('06/06/16', 27)
,('01/21/16', 5.5)
,('10/23/15', 5.5)
,('07/20/15', 5.5)
,('06/05/15', 24)
,('01/27/15', 5)
,('10/29/14', 5)
,('07/28/14', 5)
,('07/28/14', 40)
,('06/06/14', 20)
,('01/27/14', 4)
,('10/25/13', 4)
,('07/29/13', 4)
,('06/06/13', 13)
,('01/23/13', 3)
,('10/31/12', 3)
,('07/23/12', 3)
,('06/07/12', 8)
,('06/07/12', 8)
,('01/25/12', 3)
,('10/25/11', 3)
,('07/28/11', 3)
,('06/08/11', 8)
,('01/27/11', 2)
,('11/01/10', 2)
,('07/29/10', 2)
,('06/15/10', 4)
,('06/15/10', 10)
,('01/27/10', 2)
,('10/28/09', 2)
,('07/27/09', 2)
,('06/16/09', 5)
,('01/28/09', 3)
,('10/29/08', 3)
,('07/31/08', 3)
,('06/18/08', 5)
,('01/23/08', 3)
,('10/22/07', 3)
,('07/27/07', 3)

我想在每個之間得到 sum(DIVIDENDPERSHARE) 財政年度，即所有記錄的 3 月 31 日和 4 月 1 日

例如，2007 年 3 月 31 日至 2008 年 4 月 1 日之間的 2007 財政年度的股息總額為 9，這應該對所有記錄進行迭代記錄。

以下代碼確實有效：

DECLARE @STARTDATE DATE
DECLARE @ENDDATE DATE
SET @STARTDATE = '04/01/2007'
SET @ENDDATE = '03/31/2008'

WHILE YEAR(@ENDDATE) <= YEAR(GETDATE())
BEGIN   
    SELECT SUM(DIVIDENDPERSHARE) AS DIVIDEND, @STARTDATE AS STARTING_DATE, @ENDDATE AS ENDING_DATE FROM
    @TCS_DIVIDEND_PER_YEAR 
    WHERE RECORD_DATE BETWEEN @STARTDATE AND @ENDDATE

    Set @STARTDATE = DATEADD(YYYY,1, @STARTDATE)
    SET @ENDDATE = DATEADD(YYYY, 1, @ENDDATE)

    IF @@ROWCOUNT = 0
    BREAK;
END

輸出為

(58 rows affected)
DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
9           2007‑04‑01    2008‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
14          2008‑04‑01    2009‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
11          2009‑04‑01    2010‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
20          2010‑04‑01    2011‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
17          2011‑04‑01    2012‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
25          2012‑04‑01    2013‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
25          2013‑04‑01    2014‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
75          2014‑04‑01    2015‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
39          2015‑04‑01    2016‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
45          2016‑04‑01    2017‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
48          2017‑04‑01    2018‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
41          2018‑04‑01    2019‑03‑31

(1 row affected)

DIVIDEND    STARTING_DATE ENDING_DATE
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑
85          2019‑04‑01    2020‑03‑31

(1 row affected)

是否有其他方法可以在不使用日曆表等附加表的情況下進行並根據給定的信息，如果是，請分享我想學習

參考解法

方法 1:

You don't actually need a calendar table for this. If the fiscal year starts on April 1, you can just subtract 3 months and extract the year:

SELECT v.FiscalYear, SUM(dpy.DIVIDENDPERSHARE),
       DATEFROMPARTS(v.FiscalYear, 4, 1) as StartFiscalYear,
       DATEFROMPARTS(v.FiscalYear, 3, 31) as EndFiscalYear
FROM TCS_DIVIDEND_PER_YEAR dpy CROSS APPLY
     (VALUES (YEAR(DATEADD(MONTH, ‑3, dpy.RECORD_DATE)))) v(FiscalYear)
GROUP BY v.fiscalYear;

This should be much faster than using a calendar table.

方法 2:

Create a calendar table if you don't have one already. Make sure it has fiscal year as one of the columns. Then this can be solved with a very simple query:

SELECT ct.FiscalYear,sum(div.DIVIDENDPERSHARE),min(ct.FullDate) as StartFiscalYear,max(ct.FullDate) as EndFiscalYear
FROM CalendarTable ct
LEFT JOIN TCS_DIVIDEND_PER_YEAR div
ON ct.FullDate = div.RECORD_DATE
GROUP BY ct.fiscalYear

(by Sreedhar Danturthi、Gordon Linoff、Wouter)

參考文件

1. Alternative ways to get records between a time frame in SQL (calculate fiscal year) (CC BY‑SA 2.5/3.0/4.0)