CODEWITHSUNDEEP
sqloracle-databaseoracle11g
BPL
BPL

Reputation: 33

SQL query to find number of successes between failures

I have an oracle database table that contains test result records. Each record contains the test START_TIME, the INSTRUMENT that the test was performed on, and an ERROR_CODE if an error occurred during the test, among other information.

For every record with an ERROR_CODE equal to '5900', '6900' or '5905', I need to determine the number of successful tests (ERROR_CODE = null) that have occurred on that INSTRUMENT before the datetime of the error record. In other words, I need to know the number of successful tests performed on the instrument before an error was generated.

The database contains over 500 instruments that can each have between 1 and 500,000 test records.

Notes: Only interested in number of successes before ERROR_CODES '5900', '6000' and '5905'. Some instruments may have zero of those errors. Some instruments may have multiple consecutive errors, with no success between them. An error may have occurred on that instrument's first or last test.

Example:

START_TIME          INSTRUMENT  ERROR_CODE
12/1/2015 22:15:03  A540        null
12/1/2015 22:17:14  A700        null
12/1/2015 22:17:53  A700        null
12/1/2015 22:19:24  A700        5905
12/1/2015 23:28:15  A700        null
12/1/2015 23:35:10  A540        6000
12/2/2015 02:15:13  A540        5900
12/2/2015 03:07:03  A540        null
12/2/2015 03:44:52  A540        null
12/2/2015 09:15:56  A700        null
12/2/2015 14:17:09  A700        5900
12/2/2015 17:15:42  A980        null
12/3/2015 08:17:53  A540        5900
12/3/2015 08:18:49  A540        5900
12/3/2015 11:17:57  A540        null

should give the following results

ERROR_TIME          INSTRUMENT  SUCCESSES_BEFORE_ERROR
12/1/2015 22:19:24  A700        2
12/1/2015 23:35:10  A540        1
12/2/2015 02:15:13  A540        1
12/2/2015 14:17:09  A700        4
12/3/2015 08:17:53  A540        3
12/3/2015 08:18:49  A540        3

Upvotes: 3

Views: 490

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

There may be a way to do this with analytic functions (no doubt there is). But the simplest way to express the logic -- in my opinion -- is to use a correlated subquery:

select t.*,
       (select count(*)
        from t t2
        where t2.instrument = t.instrument and
              t2.start_time < t.start_time and
              t2.error_code is null
       ) as SUCCESSES_BEFORE_ERROR
from t
where t.error_code is not null;

Upvotes: 0

Michael Piankov
Michael Piankov

Reputation: 1997

I dont know source table name I call it table_one.

EDIT: As I see now I make a mistake I calculate cosequece of successfull test. I leave it as is 

ordered_tab as ( 
select  START_TIME           
       ,INSTRUMENT 
       ,ERROR_CODE 
       ,row_number() over (partition by INSTRUMENT order by START_TIME) rn 
  from table_one) 
 select START_TIME  as ERROR_TIME      
       ,INSTRUMENT 
       ,SUCCESSES_BEFORE_ERROR
   FROM ( 
     select START_TIME           
           ,INSTRUMENT 
           ,ERROR_CODE 
           ,rn -1 
           - nvl(last_value(nvl2(ERROR_CODE,rn,null) ignore nulls) 
             over (partition by INSTRUMENT order by START_TIME rows between unbounded preceding and 1 preceding),0) as SUCCESSES_BEFORE_ERROR
      from ordered_tab 
  ) where ERROR_CODE IN (5905, 5900, 6000)

Upvotes: 0

Boneist
Boneist

Reputation: 23588

Here's a way using analytic functions:

WITH test_results AS (SELECT to_date('12/01/2015 22:15:03', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, NULL ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/01/2015 22:17:14', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, NULL ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/01/2015 22:17:53', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, NULL ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/01/2015 22:19:24', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, 5905 ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/01/2015 23:28:15', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, NULL ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/01/2015 23:35:10', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, 6000 ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/02/2015 02:15:13', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, 5900 ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/02/2015 03:07:03', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, NULL ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/02/2015 03:44:52', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, NULL ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/02/2015 09:15:56', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, NULL ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/02/2015 14:17:09', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, 5900 ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/02/2015 17:15:42', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A980' instrument, NULL ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/03/2015 08:17:53', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, 5900 ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/03/2015 08:18:49', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, 5900 ERROR_CODE FROM dual UNION ALL
                      SELECT to_date('12/03/2015 11:17:57', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, NULL ERROR_CODE FROM dual)
-- end of mimicking a table with data in it called "test_results"
-- for use in the following select statement:
SELECT start_time,
       instrument,
       running_total success_before_error
FROM   (SELECT start_time,
               instrument,
               ERROR_CODE,
               sum(CASE WHEN ERROR_CODE IS NOT NULL THEN 0
                        ELSE 1
                   END) OVER (PARTITION BY instrument ORDER BY start_time) running_total
        FROM   test_results)
WHERE  ERROR_CODE IS NOT NULL -- this may need to be "error_code in (5900, 6000, 5905)"
ORDER BY start_time;

START_TIME          INSTRUMENT SUCCESS_BEFORE_ERROR
------------------- ---------- --------------------
12/01/2015 22:19:24 A700                          2
12/01/2015 23:35:10 A540                          1
12/02/2015 02:15:13 A540                          1
12/02/2015 14:17:09 A700                          4
12/03/2015 08:17:53 A540                          3
12/03/2015 08:18:49 A540                          3

Upvotes: 2

Related Questions