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 |
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;