SQL - 运行总计 - 年初至今、上一年迄今为止和最后滚动 12 个月 [英] SQL - Running Total - Year To Date, Previous Year To Date, and Last Rolling 12 Months

问题描述

谢谢你帮我解决这个问题！我提前为格式道歉，但我认为它很容易理解.

Thanks for helping me out with this one! I apologize in advance for the formatting, but I think it is easy to follow.

提供了一个示例数据表.我需要使用查询语言创建三个单独的列.运行总数必须考虑 ID 和日期.

Provided is a sample table of data. I need three separate columns created with query language. The running total must go consider the ID and the date.

运行 12 个月 - 我需要对滚动 12 个月的每个 ID 的值求和.

Running 12 Months - I need sum the value of each ID for rolling 12 months.

当前 YTD - 对值的日期的当前年份的每个 ID 的值求和.

Current YTD - Sum the value of each ID for the current year of the date of the value.

上一个 YTD - 对每个 ID 值的日期前一年的值求和.

Previous YTD - Sum the value of each ID for the year immediately prior to the date of the value.

谢谢！

当前表格

ID  DATE        VALUE
S1  01/01/2015  5
S1  02/01/2015  5
S1  03/01/2015  5
S1  04/01/2015  5
S1  05/01/2015  5
S1  06/01/2015  5
S1  07/01/2015  5
S1  08/01/2015  5
S1  09/01/2015  5
S1  10/01/2015  5
S1  11/01/2015  5
S1  12/01/2015  5
S1  01/01/2016  5
S2  01/01/2015  10
S2  02/01/2015  10
S2  03/01/2015  10
S2  04/01/2015  10
S2  05/01/2015  10
S2  06/01/2015  10
S2  07/01/2015  10
S2  08/01/2015  10
S2  09/01/2015  10
S2  10/01/2015  10
S2  11/01/2015  10
S2  12/01/2015  10
S2  01/01/2016  10

期望的输出

ID  DATE            VALUE   Running12   CalendarYTD PrevCalendarYTD
S1  01/01/2015      5       5           5   
S1  02/01/2015      5       10          10  
S1  03/01/2015      5       15          15  
S1  04/01/2015      5       20          20  
S1  05/01/2015      5       25          25  
S1  06/01/2015      5       30          30  
S1  07/01/2015      5       35          35  
S1  08/01/2015      5       40          40  
S1  09/01/2015      5       45          45  
S1  10/01/2015      5       50          50  
S1  11/01/2015      5       55          55  
S1  12/01/2015      5       60          60  
S1  01/01/2016      5       60          5              5
S2  01/01/2015      10      10          10  
S2  02/01/2015      10      20          20  
S2  03/01/2015      10      30          30  
S2  04/01/2015      10      40          40  
S2  05/01/2015      10      50          50  
S2  06/01/2015      10      60          60  
S2  07/01/2015      10      70          70  
S2  08/01/2015      10      80          80  
S2  09/01/2015      10      90          90  
S2  10/01/2015      10      100         100
S2  11/01/2015      10      110         110 
S2  12/01/2015      10      120         10  
S2  01/01/2016      10      120         10              10

推荐答案

VKP 提出了一个有效的观点.如果缺少月份，我们需要补上.

以下将生成您的运行总计列.

The following will generate your running total columns.

示例

Select *
      ,Running12       = sum(Value) over (Partition By ID Order By Date Rows Between 11 Preceding and Current Row)
      ,CalendarYTD     = sum(Value) over (Partition By ID,Year(Date) Order By Date)
      ,PrevCalendarYTD = case when month(date)<>1 then null else (Select Value from @YourTable Where ID=A.ID and date=dateadd(year,-1,A.date)) end
 From @YourTable A
 Order By ID,Date

退货

这篇关于SQL - 运行总计 - 年初至今、上一年迄今为止和最后滚动 12 个月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！