Rolling Sum in SQL Server

7hiiyaii 于 2023-08-02 发布在 SQL Server

关注(0)|答案(1)|浏览(119)

I am looking at creating a rolling sum in SQL Server similar to the Excel breakout below. The issue I am unable to work around (after primarily utilizing the lag function) is that I need to start summing the daily extra and the previous rollover. There is an Excel table similar to what I am trying to achieve along with code I have attempted.

select *,
case when lag([daily_extra],1) over (order by [date]) = 0 and [daily_extra] > 0 then
[daily_extra] else 
        case when lag([daily_extra],1) over (order by [date]) > 0 and [daily_extra] > 0 then
        [daily_extra]+lag([daily_extra],1) over (order by [date]) else 0 end end [Rollover]
from
(
select [date],
[tickets_sold] [TICKETS],
[max_tickets] [MAX TICKETS],
case when [tickets_sold]-[max_tickets] > 0 then [tickets_sold]-[max_tickets] 
    else 0 end [Daily Extra]
from table
) t1

sql-server

来源：https://stackoverflow.com/questions/76759034/rolling-sum-in-sql-server

1条答案

按热度按时间

du7egjpx1#

As mention in the comment, you need to use recursive cte as your rollover value is depends on previous calculated value

with 
cte as
(
    select rn = row_number() over (order by [date]),
           [date], tickets_sold, max_tickets
    from   [table]
),
rcte as
(
    select rn, [date], tickets_sold, max_tickets,
           daily_extra = case when tickets_sold > max_tickets
                              then tickets_sold - max_tickets
                              else 0
                              end,
           rollover    = case when tickets_sold > max_tickets
                              then tickets_sold - max_tickets
                              else 0
                              end
    from   cte
    where  rn = 1

    union all

    select c.rn, c.[date], c.tickets_sold, c.max_tickets,
           daily_extra = case when c.tickets_sold > c.max_tickets
                              then c.tickets_sold - c.max_tickets
                              else 0
                              end,
           rollover    = case when r.rollover + c.tickets_sold - c.max_tickets > 0
                              then r.rollover + c.tickets_sold - c.max_tickets
                              else 0
                              end
    from   cte c
           inner join rcte r on c.rn = r.rn + 1
)
select *
from   rcte
order by [date]

赞(0）回复(0）举报 2023-08-02

我来回答

Rolling Sum in SQL Server

1条答案

相关问题

热门标签

最新问答