SQL-运行总计-年初至今，上一年迄今和最近12个月

小编典典

SQL-运行总计-年初至今，上一年迄今和最近12个月

sql

感谢您帮助我解决这一问题！我预先为格式表示歉意，但我认为这很容易理解。

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

连续运行12个月-我需要汇总每个ID的值才能滚动12个月。

当前YTD-将值日期的当前年份的每个ID的值相加。

上一个YTD-总计每个ID的值，该值应为该日期之前的一年。

谢谢你！

电流表

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

阅读 269

2021-04-15

共1个答案

小编典典

VKP提出了一个有效的观点。如果缺少几个月，我们将需要填补。

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

例子

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

2021-04-15