Reset Row Number with two conditions

Database

Reset Row Number with two conditions

Hello,

 

I have the requirements to look at a person, their eligibility dates and their products.  Whenever the months in between are NOT continuous, or whenever the product changes, I need the row number to reset back to one.

 

Example:

SEL

PERSON_NO

, PRODUCT

, ELIG_DT

, DENSE_RANK()  OVER

    (   PARTITION BY PERSON_NO, PRODUCT

                 ORDER BY ELIG_DT 

                 RESET WHEN PRODUCT <> AVG(PRODUCT)

                           AND ELIG_DT > MIN(ELIG_DT)

                          

                         

                         OVER (

 

             PARTITION BY PRODUCT

 

             ORDER BY ELIG_DT DESC

                                   

            ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING   )  ) 

 

                        AS RN

           

FROM TABLE1

WHERE PERSON_NO = '9—‘

  AND PRODUCT LIKE ANY ('599','4%')

    AND ELIG_DT BETWEEN '20150501' AND '20160401'

            GROUP BY 1,2,3

            ORDER BY 3 DESC;

 

 

 

What happens is:

 

Product a, which begin as of 5/1/2015, is good for five months (5/1, 6/1, 7/1, 8/1, and 9/1) and then it moves to product b, which starts on 10/1 and stops after 11/1.  When product a resumes on 12/1/2016, the row number continues on with "6" when it should reset back to 1 because the months are not continuous or the product has changed.

 

TeradataExample_19092016.gif

 

Please advise the best way to do this.

 

Thank you,

Sean

3 REPLIES
Junior Contributor

Re: Reset Row Number with two conditions

Can you show som edata plus expected result, your current query is not valid, AVG on a VarChar?

Re: Reset Row Number with two conditions

Hi Dnoeth,

 

I pasted the data in the previous posting.  The issue is that the 12/1/2015 record should not have row number (column RN) showing 6; rather, it should re-start at 1 because either the product changed (from 425 to 599) or the ELIG_DT is not the next month following.  In this instance, it should change only because of the product changing.

 

Let me know if I can provide more context, and thank you, in advance, for this help.

 

Sean

Junior Contributor

Re: Reset Row Number with two conditions

You should post the table DDL (again, your query is not valid SQL), some rows (best case using Inserts) and expected result.