纳税年度 7 月至 6 月的 SQL 累计总数

Question

尝试在最右侧的列中获取累计总数，该列将重置为每年 7 月重新开始。纳税年度为七月至六月。此代码重置日历年。:

Select T.MonthlyTotal
      ,T.SalesMonth
      ,T.[Year]
      ,SUM(T.MonthlyTotal) OVER (ORDER BY T.[Year]) AS CumulativeTotal 
        From (SELECT 
        SUM (SubTotal) AS MonthlyTotal
        ,MONTH(CreateDate) AS SalesMonth
        ,Year(CreateDate) AS [Year]
        FROM OrderFormHeader
        Group by Month(CreateDate),Year(CreateDate)) AS T

  Order by T.[Year],T.SalesMonth

示例数据:

MonthlyTotal    SalesMonth  Year    CumulativeTotal
34370.56               7    2009    135682.15
61915.29               8    2009    135682.15
15027.21               9    2009    135682.15
9537.80               10    2009    135682.15
6748.38               11    2009    135682.15
8082.91               12    2009    135682.15
9047.77                1    2010    446574.06
11152.21               2    2010    446574.06
11672.16               3    2010    446574.06
13451.61               4    2010    446574.06
10777.37               5    2010    446574.06
20135.99               6    2010    446574.06
55169.70               7    2010    446574.06
93018.89               8    2010    446574.06
50195.15               9    2010    446574.06
11842.17               10   2010    446574.06

Answer 1

您需要更改数据的分区方案:

select SUM(SubTotal) AS MonthlyTotal, MONTH(CreateDate) AS SalesMonth,
       Year(CreateDate) AS [Year],
       SUM(SUM(SubTotal)) OVER (PARTITION BY MIN(Year(DATEADD(month, 6, CreateDate)))
                                ORDER BY MIN(MONTH(DATEADD(month, 6, CreateDate)))
                               ) as FY_YTD
from OrderFormHeader
Group by Month(CreateDate), Year(CreateDate)
Order by [Year], SalesMonth;

您可以使用在日期上加上六个月的技巧来获得“纳税”月份。然后，您可以使用此信息提取累积和的年份和月份。

请注意，您不需要子查询。您可以将聚合函数与窗口函数混合使用。