-2

I have data like below and I need to somewhat generate running total but not really. When value changes from Green to either (Yellow or Red) then 1 for all rows until it changes again from Green to either Red or Yellow then 2 for all rows until it changes.

I think I can use lag function to check the previous row vs the current row but not sure how I can get this output.

value output
Black 0
Blue 0
Green 0
Yellow 1
Black 1
Green 1
Red 2
Black 2
Blue 2
Green 2
Green 2
Yellow 3
GMB
  • 216,147
  • 25
  • 84
  • 135
user3456025
  • 1
  • 3
  • 1
    While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. – Yitzhak Khabinsky Apr 03 '23 at 21:22
  • 1
    Why not have a go and see how you get on, have a play with lag, read up on gaps-and-islands and have a go? You might surprise yourself. – Dale K Apr 03 '23 at 21:28
  • 2
    How would you order the rows? Rememer SQL tales are unordered sets unless you use `ORDER BY` – Charlieface Apr 03 '23 at 21:28

1 Answers1

0

This is a gaps-and-islands variant. Assuming that you have a column to order the records, say id, we can identify the transitions with a lag and a window sum:

select id, val,
    sum(case when lag_val = 'Green' and val != 'Green' then 1 else 0 end) 
        over(order by id) grp
from (
    select t.*, lag(val, 1, val) over(order by id) lag_val
    from mytable t
) t
order by id
id val grp
1 Black 0
2 Blue 0
3 Green 0
4 Yellow 1
5 Black 1
6 Green 1
7 Red 2
8 Black 2
9 Blue 2
10 Green 2
11 Green 2
12 Yellow 3

fiddle

GMB
  • 216,147
  • 25
  • 84
  • 135