teradataolappartition

TERADATA: Is it possible to ignore rows in an OLAP partition when the condition is met and still pass the value down when it isn't met?


I'm partitioning data based on a customers previous order, so if the customer previously added a service to their account (they either have the service or they don't), I want that value to carry down to the next row for that customer for all orders regardless of the order status, but I don't want canceled order services to be calculated with the next order, I want to skip those rows and bring down the value from the previously completed order. Does anyone know if this is possible? If I add the field into the Partition By clause, it'll partition by order status instead of reporting the order status from the previous completed order.

(
Sum
    (
    SUBSCR1_ORD
    )
    Over 
        (
        PARTITION BY ACCT_NO
        ORDER BY ORDER_DATE
        ROWS BETWEEN 1 Preceding AND 1 Preceding
        )
)
AS EXISTING_SVC1

This is what I'd want the results to look like for the EXISTING_SVC columns based on activity in the SUBSCR1_ORD column with special handing on ORDER_STATUS

ACCT_NO ORDER_DATE ORDER_STATUS SUBSCR1_ORD SUBSCR2_ORD EXISTING_SVC1 EXISTING_SVC2
1234 6/5/2022 Complete 1 null 0 0
1234 6/6/2022 Canceled -1 1 1 0
1234 6/7/2022 Complete null 1 1 0

Solution

  • Use LAG with IGNORE NULLS and a CASE expression to "pull down" the prior value.

    SELECT Acct_No, Order_Date, Order_Status, Subscr1_Ord, Subscr2_Ord,
        LAG(CASE WHEN Order_Status='Canceled' THEN NULL ELSE Subscr1_Ord END,1,0)
            IGNORE NULLS
        OVER(PARTITION BY Acct_No ORDER BY Order_Date)
        AS Existing_Svc1,
        LAG(CASE WHEN Order_Status='Canceled' THEN NULL ELSE Subscr2_Ord END,1,0)
            IGNORE NULLS
        OVER(PARTITION BY Acct_No ORDER BY Order_Date)
         AS Existing_Svc2   
    FROM MyTable
    ORDER BY Order_Date;