I have a source table that contains run times for several items and resultant error codes during that run. The result I'm looking for should show how many successive runs each error occurs for every item. If the error skips a run, the count resets. Runs are not always run on successive days. The idea is to find out the error codes occur continuously without being reset for every item.
Item Run Run_date Err_Code
------------------------------------------
A 1 1/1/2020 ERR1
A 1 1/1/2020 ERR2
A 1 1/1/2020 ERR3
A 2 1/2/2020 ERR1
A 2 1/2/2020 ERR2
A 3 1/3/2020 ERR1
A 3 1/3/2020 ERR2
A 4 1/4/2020 ERR2
A 4 1/4/2020 ERR3
A 5 1/5/2020 ERR1
A 5 1/5/2020 ERR3
B..
B..
B..
Result: 3 cols
Item Err_Cd Times
---------------------
A ERR1 3 (meaning ERR1 occurred thrice in succession for A in runs 1,2,3)
A ERR1 1 (meaning ERR1 occurred once for A in run 5)
A ERR2 4 (meaning ERR2 occurred 4 times in succession for A in runs 1,2,3,4)
A ERR3 1 (meaning ERR3 occurred once for A in run 1)
A ERR3 2 (meaning ERR3 occurred twice in succession for A in runs 4,5)
B..
B..
Appreciate any help! Thanks.
You can subtract row_number() from the date and aggregate:
select item, err_cd, count(*)
from (select t.*,
row_number() over (partition by item, err_cd order by run) as seqnum
from t
) t
group by item, err_cd, run - seqnum
order by item, err_cd, min(run);