CODEWITHSUNDEEP
sqlsql-server-2012
Nitesh_R
Nitesh_R

Reputation: 55

SQL - Flag consecutive value change in column

I'm going to do my best to explain my issue.

I have a dataset that looks similar to the following:

Row#    CusCode Status
1   100101  Green
2   100101  Green
3   100101  Red
4   100101  Amber
5   100101  Amber
6   100101  Green
7   100101  Red
8   100101  Red
9   100101  Red
10  200909  Red
11  200909  Amber
12  200909  Green
13  200909  Red

I am trying to get this view as a next step:

Row#    CusCode Status  Required
1   100101  Green   1
2   100101  Green   1
3   100101  Red 2
4   100101  Amber   3
5   100101  Amber   3
6   100101  Green   4
7   100101  Red 5
8   100101  Red 5
9   100101  Red 5
10  200909  Red 1
11  200909  Amber   2
12  200909  Green   3
13  200909  Red 4

Last step is for the dataset to look as follows:

CusCode Status  Required
100101  Green   1
100101  Red 2
100101  Amber   3
100101  Green   4
100101  Red 5
200909  Red 1
200909  Amber   2
200909  Green   3
200909  Red 4

Please note the following:

Your assistance will be greatly appreciated. :)

Upvotes: 2

Views: 1590

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35623

For the first phase of this I would use LEAD() and then this allows me to locate each row that does (or does not) have a change in status. So then delete the unwanted rows, and voila! you have your end result by using ROW_NUMBER() on the surviving rows.

Regarding the "original order". You should never rely on the rows of a table being stored in a predictable order. Below I have used a "trick" to fool the over clause to apply an order that in effect does nothing and so "hopefully" the rows will be in the desired "original order", however this cannot be guaranteed. In practice it works, but you really should not rely on it. Add a datetime/datetime2 column as each row is created.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Table1
    ([RowNo] int, [CusCode] int, [Status] varchar(5))
;

INSERT INTO Table1
    ([RowNo], [CusCode], [Status])
VALUES
    (1, 100101, 'Green'),
    (2, 100101, 'Green'),
    (3, 100101, 'Red'),
    (4, 100101, 'Amber'),
    (5, 100101, 'Amber'),
    (6, 100101, 'Green'),
    (7, 100101, 'Red'),
    (8, 100101, 'Red'),
    (9, 100101, 'Red'),
    (10, 200909, 'Red'),
    (11, 200909, 'Amber'),
    (12, 200909, 'Green'),
    (13, 200909, 'Red')
;

Delete Query:

with CTE as (
  select
       *
     , lead(status) over(partition by CusCode order by (select 1)) nxt_status
  from table1
  )
delete from CTE
where status = nxt_status
;

Inspect Query

with CTE as (
  select
       *
     , row_number() over(partition by CusCode order by (select 1)) rn
  from table1
  )
select
*
from CTE
;

Results:

| RowNo | CusCode | Status | rn |
|-------|---------|--------|----|
|     2 |  100101 |  Green |  1 |
|     3 |  100101 |    Red |  2 |
|     5 |  100101 |  Amber |  3 |
|     6 |  100101 |  Green |  4 |
|     9 |  100101 |    Red |  5 |
|    10 |  200909 |    Red |  1 |
|    11 |  200909 |  Amber |  2 |
|    12 |  200909 |  Green |  3 |
|    13 |  200909 |    Red |  4 |

Upvotes: 3

Related Questions