Search code examples
sqlt-sqlteradatarow-number

Condition evaluation and applying row numbers in a table

I need guidance to resolve following issue. I am trying to give row number based on condition. Here is my condition:

ROW_NUMBER() OVER (
    PARTITION BY CLAIM_KEY, EXPOSURE_KEY, RESERVELINE_ID, TRANSACTIONSUBTYPE_DESC 
    ORDER BY TRANSACTION_CREATE_TS
)

Here is my table after applying above condition. (I just included one example here to simplify things.)

enter image description here

And here is what I want to achieve, but so far no results.

enter image description here

Solution

  • I think you will need to consider using RESET WHEN as part of your ROW_NUMBER() window aggregate. RESET WHEN can include another window aggregate to allow you to look back at the previous row in the scope of the existing partition of ROW_NUMBER to check the condition of either the amount or close time stamp being NOT NULL.

    The use of RESET WHEN is explained in Chapter 22 of SQL Functions, Operators, Expressions, and Predicates manual for Teradata 15.10. The chapter title is Ordered Analytical/Windows Aggregate Functions - The Window Feature.

    Hope this helps set you in the right direction.