Reputation: 55
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:
I require that a counter be created based on the status change, irrespective if the status changes to an earlier status, intention is to flag consecutive duplicate records - Table 2 provides an illustration of this.
The dataset contains multiple records
The order in which the Status is displayed is most important and must output in the same order (Table 3 provides this view).
Your assistance will be greatly appreciated. :)
Upvotes: 2
Views: 1590
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.
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
;
| 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