SQL Server SSMS rolling count based on value resetting at 0

9jyewag0 于 2023-06-28 发布在其他

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

Trying to get a rolling count that resets when 0 is hit.

Only need a few fields.

Ordered by the date field and then ref field
when value goes 0 to >0 count begins at 1
If value remains same or increases count goes to 2 and beyond
if value goes to 0 count goes back to 0
repeats if value changes to >0 again at 1

Date:

Date   Ref    Value   Count
202201 170532 £300.21 1
202202 170532 £600.42 2
202203 170532 £0.00   0
202204 170532 £300.21 1
202205 170532 £600.42 2
202206 170532 £900.63 3

.. ..

Tried a few things i've read on here but not having much joy.

Thanks for any help or direction provided!

sql-server

来源：https://stackoverflow.com/questions/76557449/ssms-rolling-count-based-on-value-resetting-at-0

1条答案

按热度按时间

mbjcgjjk1#

Put your rows into groups first, by COUNT ing how many times 0 has been in the dataset prior, and then you can use a ROW_NUMBER on that group. You'll need to use couple of CASE expressions as well to put 0 in the right place.:

WITH Grps AS(
    SELECT Date,
           Ref,
           Value,
           COUNT(CASE Value WHEN 0 THEN 1 END) OVER (ORDER BY Date ASC) AS Grp
    FROM (VALUES(202201,170532,300.21),
                (202202,170532,600.42),
                (202203,170532,0.00  ),
                (202204,170532,300.21),
                (202205,170532,600.42),
                (202206,170532,900.63))V(Date,Ref,Value))
SELECT Date,
       Ref,
       Value,
        CASE Value WHEN 0 THEN 0 ELSE ROW_NUMBER() OVER (PARTITION BY CASE Value WHEN 0 THEN 0 END, Grp ORDER BY Date ASC) END AS RN
FROM Grps
ORDER BY Date ASC;

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

我来回答

SQL Server SSMS rolling count based on value resetting at 0

1条答案

相关问题

热门标签

最新问答