I have a tables that holds the reconcilliation results while comparing sam etables from different databases and holds the results as PASS and FAIL.
I want to count the results with CONSECUTIVE passes. Data is mentioned below.
|Scenario 1||Scenario 2|
|DBNAME||TB NAME||STATUS||DT||DBNAME||TB NAME||STATUS||DT|
Scenario 1 should give me the pass count as 3 as it failed before that . Scenario 2 should give me the passcount six as it had 5 consecutive seccusseful runs. If I just use ow_numbert it doesn't acoount for the FAIL and will count all the PASS.
Select OWNER,OBJECT_NAME,ROW_NUMBER() OVER ( PARTITION BY OWNER,OBJECT_NAME ORDER BY R2T_DATE DESC), so for scenario 1 it will give COUNT for PASS = 5 which is wrong as it should be 3 beacause run failed before that.
FOR scenario 2 it will give the account as 6 which is fine as all 6 runs passed. Can any one please help with this.
The data looks like below