SQL Server ;How to create running total that resets upon a NULL value T-SQL

dldeef67 于 2023-06-21 发布在其他

关注(0)|答案(2)|浏览(160)

I am trying to create a running total column that resets each time it encounters a null or multiple null values. I expect to maintain the ID partition and date order. I expect the result as the table below
| ID | date | value | Running_T |
| ------------ | ------------ | ------------ | ------------ |
| SA223 | 10/07/2018 | 3 | 3 |
| SA223 | 11/07/2018 | 6 | 9 |
| SA223 | 25/07/2018 | NULL | NULL |
| SA223 | 16/08/2018 | 11 | 11 |
| SA223 | 17/08/2018 | 9 | 20 |
| SA555 | 15/05/2019 | 4 | 4 |
| SA555 | 27/05/2019 | 13 | 17 |
| SA555 | 11/07/2019 | NULL | NULL |
| SA555 | 21/11/2019 | NULL | NULL |
| SA555 | 20/02/2020 | 4 | 4 |
| SA555 | 27/05/2020 | 1 | 5 |
| SA555 | 23/07/2020 | 2 | 7 |

I tried the code below but it messed up the ordering and does not deal with Null values as I want.

SELECT ID
       , date
       , value
       ,Running_T = SUM(value) OVER(PARTITION BY ID, (CASE WHEN [value] IS NOT NULL THEN [value] ELSE NULL END) ORDER BY [date])
FROM dim.event_data

sql-server

来源：https://stackoverflow.com/questions/76461255/how-to-create-running-total-that-resets-upon-a-null-value-t-sql

2条答案

按热度按时间

ipakzgxi1#

This is a classic Gaps And Islands problem. There are more efficient solutions but a simple one is as follows:

You can use a conditional running COUNT to number each set of rows. Then do a running SUM over that group number to get the final result

WITH Counted AS (
    SELECT *,
      GrpId = COUNT(CASE WHEN value IS NULL THEN 1 END)
              OVER (PARTITION BY ID ORDER BY date ROWS UNBOUNDED PRECEDING)
    FROM event_data ed
)
SELECT *,
    Running = SUM(value) OVER (PARTITION BY ID, GrpId ORDER BY Date)
FROM Counted;

db<>fiddle

赞(0）回复(0）举报 2023-06-21

bkhjykvo2#

you can try like this

SELECT ID,
    date,
    value,
    CASE
        WHEN value IS NULL THEN NULL
        ELSE SUM(value) OVER (PARTITION BY ID ORDER BY date) 
    END AS Running_T
FROM YourTable;

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server ;How to create running total that resets upon a NULL value T-SQL

2条答案

相关问题

热门标签

最新问答