By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE example (
sro_num VARCHAR(512),
seq INT,
stat_code VARCHAR(512)
);
INSERT INTO example (sro_num, seq, stat_code) VALUES
('SO00053427', '1', 'NEW'),
('SO00053427', '2', 'READY'),
('SO00053427', '3', 'PICKED'),
('SO00053427', '4', 'SHIPPED'),
('SO00053427', '5', 'INVOICED'),
('SO00053441', '1', 'NEW'),
('SO00053441', '2', 'INV HOLD'),
('SO00053441', '3', 'PICKED'),
('SO00053441', '4', 'PARTIALSHP'),
('SO00053441', '5', 'BILLMGRHLD'),
('SO00053441', '6', 'INV HOLD'),
('SO00053441', '7', 'PARTIALSHP'),
('SO00053441', '8', 'PARTIALINV'),
('SO00053441', '9', 'BILLMGRHLD'),
('SO00053441', '10', 'INV HOLD'),
('SO00053441', '11', 'READY'),
('SO00053441', '12', 'PICKED'),
('SO00053441', '13', 'SHIPPED'),
('SO00053441', '14', 'INVOICED');
SELECT * FROM Example
| sro_num | seq | stat_code |
|---|---|---|
| SO00053427 | 1 | NEW |
| SO00053427 | 2 | READY |
| SO00053427 | 3 | PICKED |
| SO00053427 | 4 | SHIPPED |
| SO00053427 | 5 | INVOICED |
| SO00053441 | 1 | NEW |
| SO00053441 | 2 | INV HOLD |
| SO00053441 | 3 | PICKED |
| SO00053441 | 4 | PARTIALSHP |
| SO00053441 | 5 | BILLMGRHLD |
| SO00053441 | 6 | INV HOLD |
| SO00053441 | 7 | PARTIALSHP |
| SO00053441 | 8 | PARTIALINV |
| SO00053441 | 9 | BILLMGRHLD |
| SO00053441 | 10 | INV HOLD |
| SO00053441 | 11 | READY |
| SO00053441 | 12 | PICKED |
| SO00053441 | 13 | SHIPPED |
| SO00053441 | 14 | INVOICED |
--inner query results
SELECT *, LAG(stat_code,1) over (partition by sro_num order by seq) as Prev_Stat
FROM Example
| sro_num | seq | stat_code | Prev_Stat |
|---|---|---|---|
| SO00053427 | 1 | NEW | null |
| SO00053427 | 2 | READY | NEW |
| SO00053427 | 3 | PICKED | READY |
| SO00053427 | 4 | SHIPPED | PICKED |
| SO00053427 | 5 | INVOICED | SHIPPED |
| SO00053441 | 1 | NEW | null |
| SO00053441 | 2 | INV HOLD | NEW |
| SO00053441 | 3 | PICKED | INV HOLD |
| SO00053441 | 4 | PARTIALSHP | PICKED |
| SO00053441 | 5 | BILLMGRHLD | PARTIALSHP |
| SO00053441 | 6 | INV HOLD | BILLMGRHLD |
| SO00053441 | 7 | PARTIALSHP | INV HOLD |
| SO00053441 | 8 | PARTIALINV | PARTIALSHP |
| SO00053441 | 9 | BILLMGRHLD | PARTIALINV |
| SO00053441 | 10 | INV HOLD | BILLMGRHLD |
| SO00053441 | 11 | READY | INV HOLD |
| SO00053441 | 12 | PICKED | READY |
| SO00053441 | 13 | SHIPPED | PICKED |
| SO00053441 | 14 | INVOICED | SHIPPED |
--Soltuion with CTE
WITH CTE as
(
SELECT *, LAG(stat_code,1) over (partition by sro_num order by seq) as Prev_Stat
FROM Example
)
SELECT *
FROM CTE
WHERE stat_code='PICKED'
AND Prev_Stat NOT IN ('READY','PARTIALSHIP')
| sro_num | seq | stat_code | Prev_Stat |
|---|---|---|---|
| SO00053441 | 3 | PICKED | INV HOLD |
--Soltuion with SubQuery
SELECT * FROM
(
SELECT *,LAG(stat_code,1) over (partition by sro_num order by seq) as Prev_Stat
FROM Example
) t
WHERE stat_code='PICKED'
AND Prev_Stat NOT IN ('READY','PARTIALSHIP')
| sro_num | seq | stat_code | Prev_Stat |
|---|---|---|---|
| SO00053441 | 3 | PICKED | INV HOLD |