Sql query to find items that had n consecutive failed attempts

Question

I only found slightly different examples I couldn't adapt to my needs due to my very limited sql skills.

I have a table with 3 revelant columns:

ItemID  Date      Result
1       1.2.2014  A
5       6.4.2014  B
9       7.4.2014  A
1       8.4.2014  A
1       9.4.2014  A
1       10.4.2014 A

I want to find the Items that had a particular result (let's say A) 3 times consecutively. In the sample above it would be Item 1. The dates are not normally consecutive.

It should work in Oracle SQL.

Many thanks for the help!

3 times consecutively based on what? date and result combination? Is there any key column? — NMK, Sep 30 '14 at 09:02
Thanks for wanting to help. I want to filter Items that have a particular result, let's say 'A' successively in time. So if Item 1 has had result A then B then A and then A, it would not be included. Only if it had A, then A and once again A. The dates can be anytime, they are not everyday or every week or month. Only the sequence is of importance. I hope I explained it good enough now. There is no key column. — Jay Christnach, Sep 30 '14 at 12:03

score 1 · Answer 1 · answered Sep 30 '14 at 09:22

SQL> WITH DATA AS(
  2  SELECT 1 ITEM_ID, TO_DATE('1.2.2014','DD.MM.YYYY') DT, 'A' RSLT FROM DUAL UNION ALL
  3  SELECT 5,       TO_DATE('6.4.2014','DD.MM.YYYY')  , 'B' RSLT FROM DUAL UNION ALL
  4  SELECT 9,       TO_DATE('7.4.2014','DD.MM.YYYY')  , 'A' RSLT FROM DUAL UNION ALL
  5  SELECT 1,       TO_DATE('8.4.2014','DD.MM.YYYY')  , 'A' RSLT FROM DUAL UNION ALL
  6  SELECT 1,       TO_DATE('9.4.2014','DD.MM.YYYY')  , 'A' RSLT FROM DUAL UNION ALL
  7  SELECT 1,       TO_DATE('10.4.2014','DD.MM.YYYY') , 'A' RSLT FROM DUAL)
  8  SELECT ITEM_ID FROM(
  9  SELECT A.*, ROW_NUMBER() OVER(PARTITION BY ITEM_ID ORDER BY RSLT) RN
 10  FROM DATA A)
 11  WHERE RN =3
 12  /

   ITEM_ID
----------
         1

SQL>

score 0 · Accepted Answer · answered Sep 30 '14 at 09:21

Not sure if this is the best way of of achieving it, but it works. I created the following table to put your example data into:

CREATE TABLE DATE_RESULT(
  ITEM_ID INT,
  DATE_COL DATE,
  RESULT_COL VARCHAR2(255 CHAR)
);

Then ran this query:

SELECT ITEM_ID FROM(
  SELECT
    ITEM_ID,
    TO_CHAR(DATE_COL,'DD-MON-YYYY') AS DATE_COL,
    RESULT_COL,
    LAG(ITEM_ID,1,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_ITEM_ID,
    LAG(ITEM_ID,2,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_ITEM_ID2,
    LAG(TO_CHAR(DATE_COL,'DD-MON-YYYY'),1,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_DATE,
    LAG(TO_CHAR(DATE_COL,'DD-MON-YYYY'),2,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_DATE2,
    LAG(RESULT_COL,1,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_RESULT,
    LAG(RESULT_COL,2,NULL) OVER (ORDER BY ITEM_ID ASC, DATE_COL ASC) AS LAST_RESULT2
  FROM 
    DATE_RESULT
)
WHERE 
  ITEM_ID = LAST_ITEM_ID
  AND ITEM_ID = LAST_ITEM_ID2
  AND TO_DATE(DATE_COL)-1 = TO_DATE(LAST_DATE)
  AND TO_DATE(DATE_COL)-2 = TO_DATE(LAST_DATE2)
  AND RESULT_COL = LAST_RESULT
  AND RESULT_COL = LAST_RESULT2;

The query uses Oracle's LAG() function to get the values from previous rows. So in this example, LAST_ITEM_ID is the item ID from the previous row, and LAST_ITEM_ID is the item ID from 2 rows previous.

In the WHERE clause I make sure that the ITEM_ID matches the previous two ITEM_IDs and that the RESULT_COL matches the previous two RESULT_COLs. I also make sure that the last two dates were consecutive.

Sorry for not describing well what I wanted to do. This is already really close. But I'm looking for item's that had a particular result let's say 'x' occuring 3 times consecutively. The dates can be anytime, there only has to be no other event than 'x' in between. Lag() seems to be the right way to go. I'll see if I can extract the meaning of this. Many thanks! — Jay Christnach, Sep 30 '14 at 12:30

score 0 · Answer 3 · answered Sep 30 '14 at 13:36

Thanks to user1578653 I figured it out. Maybe there is a more elegant way to do it, but it worked for about 500000 records in a few seconds:

SELECT ITEM_ID FROM(
   SELECT ITEM_ID, DATECOL, RESULT,
     LAG(ITEM_ID,1,NULL) OVER (ORDER BY ITEM_ID ASC, DATECOL ASC) AS LASTITEM,
     LAG(ITEM_ID,2,NULL) OVER (ORDER BY ITEM_ID ASC, DATECOL ASC) AS LASTITEM2,
     LAG(RESULT,1,NULL) OVER (ORDER BY ITEM_ID ASC, DATECOL ASC) AS LASTCODE,
     LAG(RESULT,2,NULL) OVER (ORDER BY ITEM_ID ASC, DATECOL ASC) AS LASTCODE2
   FROM
     RESULTTABLE
   )
WHERE
   ITEM_ID = LASTITEM
   AND ITEM_ID = LASTITEM2
   AND RESULT = 'A'
   AND RESULT = LASTCODE
   AND RESULT = LASTCODE2;

I was looking for a result of 'A' that occured 3 times consecutively in this example.

I'm rather happy, thanks!

Sql query to find items that had n consecutive failed attempts

3 Answers3