如何计算每个月初重置为'0‘的表的get累积值？

Question

我有一个利息列，它计算每天的利息，并显示一个月的累积结果。例如,

report_date     Interest
1/1/2021        10
2/1/2021        20
3/1/2021        30
.
.
.
1/2/2021        10

在这里，假设每天的利息是10，因此对于该月接下来的几天，它将以累积的方式显示结果。但是，在每个月的开始时，该值将被重置，并且该月的循环将重复。我的目标是计算到目前为止的累计利息。我尝试了以下方法：

DECLARE @AI float = 0



CASE WHEN report_date<> Month_First_Date (***Calculated seperately***)
    THEN @AI + Interest
     ELSE
        BEGIN
            SET @AI = @AI + Interest_lag (***previous date value:calculated separately***)
            SELECT @AI + Interest
        END 
    END AS Interest_Cummulative

Answer 1

IF EXISTS(SELECT * FROM sys.tables WHERE name='tblSample__')
DROP TABLE dbo.tblSample__
Go
 CREATE TABLE dbo.tblSample__(report_date DATETIME,interest DECIMAL(18,2))
 GO
 INSERT INTO dbo.tblSample__
SELECT  '2021/01/01 23:59:59', 1
UNION
SELECT  '2021/01/02 21:00:00', 5
UNION
SELECT  '2021/01/03 23:59:59', 1
UNION
SELECT  '2021/02/01', 6
UNION
SELECT  '2021/02/02', 10
UNION
SELECT  '2021/03/01', 12
GO
IF  NOT OBJECT_ID('TempDB..#T') IS Null
DROP TABLE #T
Go
SELECT *
,DATEADD(month, DATEDIFF(month, 0, report_date), 0) AS StartOfMonth
,DATEADD(mm, DATEDIFF(mm, 0, report_date) + 1, 0)LastOfNextMonth
INTO #T
FROM dbo.tblSample__ t
GO
SELECT * FROM #T
GO
SELECT * ,
    (SELECT SUM(ISNULL(interest,0)) 
    FROM #T tmp 
    WHERE 
        s.report_date >= tmp.StartOfMonth
        AND s.report_date < tmp.LastOfNextMonth 
        AND s.report_date >=tmp.report_date)
FROM dbo.tblSample__ s

或

SELECT * ,
        (SELECT SUM(ISNULL(interest,0)) 
        FROM (
            SELECT *
                ,DATEADD(month, DATEDIFF(month, 0, report_date), 0) AS StartOfMonth
                ,DATEADD(mm, DATEDIFF(mm, 0, report_date) + 1, 0)LastOfNextMonth
            FROM dbo.tblSample__ t
            ) tmp 
    WHERE 
        s.report_date >= tmp.StartOfMonth
        AND s.report_date < tmp.LastOfNextMonth 
        AND s.report_date >=tmp.report_date)
FROM dbo.tblSample__ s

或

SELECT * ,
        (SELECT SUM(ISNULL(interest,0)) 
        FROM (
            SELECT *
                ,DATEADD(month, DATEDIFF(month, 0, report_date), 0) AS StartOfMonth
                ,DATEADD(mm, DATEDIFF(mm, 0, report_date) + 1, 0)LastOfNextMonth
            FROM dbo.tblSample__ t
            WHERE  s.report_date >=t.report_date
            ) tmp 
    WHERE 
        s.report_date >= tmp.StartOfMonth
        AND s.report_date < tmp.LastOfNextMonth 
        )
FROM dbo.tblSample__ s

Answer 2

您可以使用SUM()窗口函数：

SELECT report_date, 
       Interest,
       SUM(Interest) OVER (PARTITION BY YEAR(report_date), MONTH(report_date) ORDER BY report_date) Interest_Cummulative
FROM tablename
ORDER BY report_date