vasdan vasdan - 13 days ago 5
SQL Question

Compare previus row to set a "StatusFlag"

I want to compare the previous row in my table on "ExtractTypeNum". So if it changed from the previous the flag should be set to "isChanged".

I have tried to develop this with a case statement but without any success.

select *
(case
when rownum = rownum-1
then
(case when
extractTypeNum <> extractTypeNum
then Null
else 'IsChanged' end)
when rownum = rownum -'1' then '3'
else '4' end) as StatusFlag
from myTable


This is the structure of the table and some sample data:

CREATE TABLE mytable(
ExtractTypeNum INTEGER NOT NULL PRIMARY KEY
,FileOrderNum VARCHAR(11) NOT NULL
,PrevFileOrderNum VARCHAR(11) NOT NULL
,NextFileOrderNum VARCHAR(11) NOT NULL
,rownum1 INTEGER NOT NULL
,Statusflag1 VARCHAR(9) NOT NULL
);
INSERT INTO mytable(ExtractTypeNum,FileOrderNum,PrevFileOrderNum,NextFileOrderNum,rownum1,Statusflag1)
VALUES (1,'2016-09-191',NULL,'2016-09-192',1,'IsInitial');
INSERT INTO mytable(ExtractTypeNum,FileOrderNum,PrevFileOrderNum,NextFileOrderNum,rownum1,Statusflag1)
VALUES (2,'2016-09-192','2016-09-191','2016-09-201',2,NULL);
INSERT INTO mytable(ExtractTypeNum,FileOrderNum,PrevFileOrderNum,NextFileOrderNum,rownum1,Statusflag1)
VALUES (1,'2016-09-201','2016-09-192','2016-09-211',3,NULL);
INSERT INTO mytable(ExtractTypeNum,FileOrderNum,PrevFileOrderNum,NextFileOrderNum,rownum1,Statusflag1)
VALUES (1,'2016-09-211','2016-09-201','2016-09-222',4,NULL);
INSERT INTO mytable(ExtractTypeNum,FileOrderNum,PrevFileOrderNum,NextFileOrderNum,rownum1,Statusflag1)
VALUES (2,'2016-09-222','2016-09-211',NULL,5,'IsLatest');

Expected output
+----------------+--------------+------------------+------------------+--------+-------------+
| ExtractTypeNum | FileOrderNum | PrevFileOrderNum | NextFileOrderNum | rownum | Statusflag1 |
+----------------+--------------+------------------+------------------+--------+-------------+
| 1 | 2016-09-191 | NULL | 2016-09-192 | | IsInitial |
| 2 | 2016-09-192 | 2016-09-191 | 2016-09-201 | | IsChanged |
| 1 | 2016-09-201 | 2016-09-192 | 2016-09-211 | | IsChanged |
| 1 | 2016-09-211 | 2016-09-201 | 2016-09-222 | | NULL |
| 2 | 2016-09-222 | 2016-09-211 | NULL | | IsLatest |
+----------------+--------------+------------------+------------------+--------+-------------+

Answer

If you are using SQL Server 2012 or later, then you can try the following query:

;WITH CTE AS (
   SELECT ExtractTypeNum, FileOrderNum, PrevFileOrderNum, 
          NextFileOrderNum, rownum1, Statusflag1,
          ROW_NUMBER() OVER (ORDER BY rownum1) AS rn,
          COUNT(*) OVER () AS totalCnt,
          LAG(ExtractTypeNum) OVER (ORDER BY rownum1) AS prevExtractTypeNum            
   FROM mytable
)
SELECT ExtractTypeNum, FileOrderNum, PrevFileOrderNum, 
       NextFileOrderNum, rownum1, Statusflag1,
       CASE 
          WHEN rn = 1 THEN 'IsInitial'
          WHEN rn = totalCnt THEN 'IsLatest'
          WHEN prevExtractTypeNum <> ExtractTypeNum THEN 'IsChanged'
       END AS StatusFlag
FROM CTE