Teradata SQL help required with ROW_NUMBER and Order Analytic functions

Database

Teradata SQL help required with ROW_NUMBER and Order Analytic functions

Hi,

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
ABC T1 PASS 3/17/2015   ABC T1 PASS 3/17/2015
  T1 PASS 3/16/2015     T1 PASS 3/16/2015
  T1 PASS 3/15/2015     T1 PASS 3/15/2015
  T1 FAIL 3/14/2015     T1 PASS 3/14/2015
  T1 PASS 3/13/2015     T1 PASS 3/13/2015
  T1 PASS 3/12/2015     T1 PASS 3/12/2015

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.

Thanks

Amjad

1 REPLY

Re: Teradata SQL help required with ROW_NUMBER and Order Analytic functions

The data looks like below 

Scenario 1

DBNAME TB NAME STATUS DT

ABC T1 PASS 3/17/2015

       T1 PASS 3/16/2015

       T1 PASS 3/15/2015

       T1 FAIL 3/14/2015

       T1 PASS 3/13/2015

        T1 PASS 3/12/2015

Scenario 2

DBNAME TB NAME STATUS DT

ABC T1 PASS 3/17/2015

       T1 PASS 3/16/2015

       T1 PASS 3/15/2015

        T1 PASS 3/14/2015

        T1 PASS 3/13/2015

         T1 PASS 3/12/2015

And there can be more tables and databases, this is just an example