row ID reset based on Value

Question

I need to generate an ID based store and reset anytime the transaction type = 200. Below is an example for my data.

StoreID     TransactionID   
A               200 
A               300
A               45
A               1
A               200
A               23
A               2000
A               200
A               1

Now, I need this to be populated. Row number that resets everytime the 200 is shown. One transaction can have multiple ids and also different numbers of transactions in between. However 200 is BeginingOftransaction so I need to to be able to get an ID and reset on the 200.

Rn  StoreID     TransactionID   
1   A               200 
2   A               300
3   A               45
4   A               1
1   A               200
2   A               23
3   A               2000
1   A               200
2   A               1

Side note: You might want to also introduce a column in your table which maintains the order of transactions. Otherwise, you won't really know when a `200` value came in which forced the counter to reset. — Tim Biegeleisen, Nov 07 '17 at 02:02
I can just display it. This is for reporting and analysis purposes only. I might store it in my local db later on. — Martin H, Nov 07 '17 at 02:18

score 1 · Answer 1 · answered Nov 07 '17 at 02:21

1

Try this code. This will reset to 1 if the transaction id is equal to 200

    SELECT 
    (CASE
        WHEN TransactionID = 200 THEN @rn := 1
        ELSE @rn := @rn + 1
    END) AS RN, StoreID, TransactionID 
    FROM TableName, (SELECT @rn := 0);

answered Nov 07 '17 at 02:21

Bluetree

1,324
2
8
25

this looked nice and clean but does not work is MS SQL. ='( – Martin H Nov 07 '17 at 19:17

score 1 · Accepted Answer · answered Nov 07 '17 at 03:02

This is for SQL Server:

Hope this is exactly what you want. Try this:

DECLARE @Tab TABLE(StoreID VARCHAR(1),id int)

INSERT INTO @Tab VALUES('A',200),('A',89),('A',89),('A',98),('A',200),('A',1),('A',3),('A',200)

SELECT SUM(1) OVER(PARTITION BY D.RES ORDER BY RN)RN,StoreID,ID
from(
    SELECT id,StoreID, RN
        ,SUM(CASE WHEN id=200 THEN 1 ELSE 0 END) OVER(ORDER BY (SELECT RN))Res
    FROM (
        SELECT id,StoreID,ROW_NUMBER() OVER(ORDER BY(SELECT NULL))RN
        FROM @Tab)D
    )D

And the result is:

RN  StoreID ID
1   A       200
2   A       89
3   A       89
4   A       98
1   A       200
2   A       1
3   A       3
1   A       200

this worked like a charm. I am using it. I never knew that we could use SUM() over (order by ). This is a huge game changer. Thank you !!!! — Martin H, Nov 07 '17 at 19:18

row ID reset based on Value

2 Answers2