SQL - Get from last reoccurring status

Question

I have a table like below

#ID ResultStatus    StatusDate
100 F               9/01/2017
100 S               6/01/2017
100 F               2/01/2017
300 F               7/01/2017
300 F               3/01/2017
300 S               1/01/2017
500 S               7/01/2017
800 F               7/01/2017
800 S               3/01/2017
800 F               2/01/2017
800 S               1/01/2017

I want to get all the 'F' records after the last 'S' record. It should just return

For ID 100 the 9/01/2017 record
For ID 300 the 3/01/2017 and 7/01/2017 records
For ID 500 nothing since there is no F
For ID 800 the 7/01/2017 record

Selecting all the failures after the last success.

I am using Teradata SQL but any SQL help would be much appreciated.

user_1144035 · Answer

The standard SQL method is:

select t.*
from t
where t.resultstatus = 'F' and
      t.statusdate > (select max(t2.statusdate)
                      from t t2
                      where t2.resultstatus = 'S' and t2.id = t.id
                     );

However, I would also be inclined to do this using window functions:

select t.*
from (select t.*,
             max(case when t.resultstatus = 'S' then statusdate end) over (partition by id) as max_s
      from t
     ) t
where t.resultstatus = 'F' and
      t.statusdate > max_s;

If you want all rows when there is no S, then change the where to:

where resultstatus = 'F' and
      (statusdate > max_s or max_s is null);

EDIT:

The following may work as well:

select t.*
from t
qualify t.resultstatus = 'F' and
        t.statusdate > max(case when t.resultstatus = 'S' then statusdate end) over (partition by id);

user_1049893 · Answer

By using CROSS JOIN, Analytic function ROW_NUMBER() we can accomplish this problem. Here is the SQL solution at the below link that would explain it in detail.

DDL :-

CREATE TABLE Sample( ID INT, ResultStatus VARCHAR(10), StatusDate DATE);

INSERT INTO Sample VALUES(100,'F','09-01-2017');
INSERT INTO Sample VALUES(100,'S','06-01-2017');
INSERT INTO Sample VALUES(100,'F','02-01-2017');

INSERT INTO Sample VALUES(300,'F','07-01-2017');
INSERT INTO Sample VALUES(300,'F','03-01-2017');
INSERT INTO Sample VALUES(300,'S','01-01-2017');

INSERT INTO Sample VALUES(500,'F','07-01-2017');

INSERT INTO Sample VALUES(800,'F','07-01-2017');
INSERT INTO Sample VALUES(800,'S','03-01-2017');
INSERT INTO Sample VALUES(800,'F','02-01-2017');
INSERT INTO Sample VALUES(800,'S','01-01-2017');

SQL :-

SELECT B.id,B.ResultStatus,B.StatusDate
  FROM
(
SELECT *,
       ROW_NUMBER() OVER( PARTITION BY ID ORDER BY StatusDate DESC ) AS rn,
       ROW_NUMBER() OVER( PARTITION BY ID ORDER BY ResultStatus DESC,StatusDate ) AS rn_status
  FROM Sample ) A
  CROSS JOIN
 (
SELECT *,
       ROW_NUMBER() OVER( PARTITION BY ID ORDER BY StatusDate DESC ) AS rn,
       ROW_NUMBER() OVER( PARTITION BY ID ORDER BY ResultStatus DESC,StatusDate DESC ) AS rn_status
  FROM Sample 
 ) B
WHERE A.ResultStatus = 'S'
  AND A.ResultStatus != B.ResultStatus
  AND B.StatusDate > A.StatusDate 
  AND A.ID = B.ID
  AND A.rn > B.rn
  AND A.rn_status = 1
  AND B.rn_status - B.rn = 1 
;

http://sqlfiddle.com/#!6/b2d17/17

SQL - Get from last reoccurring status

2 Answers