How to convert this Excel formula to snowflake query?

I have this below Excel formula and need to duplicate it in Snowflake query.

In column E

=If(B2=B1,E1-D2,C2-D2)

I used this below query.

 SELECT 
 THEATER, PARTS, Open_Repair_Orders,Non_Restricted_Empty_Bins,
 case when 
 parts = lag(parts) over (order by parts) 
 then (lag(non_restricted_empty_bins,1,0) over (partition by parts order by theater) - non_restricted_empty_bins)
 else Open_Repair_Orders - Non_Restricted_Empty_Bins end as Rpr_Order_Consumption
 FROM CX_DB.CX_GSLOBAC_STG.XXBAC_SPM_FILL_RATE_TEST;

My input is from column A to D and Column E is my desired output.

But I'm getting 0s as result after 11 in Rpr_Order_Consumption.

Can someone please help me with this problem. I can share any additional information.

Thanks in advance.

I tried with the below snowflake query.

    SELECT 
    THEATER, PARTS, Open_Repair_Orders,Non_Restricted_Empty_Bins,
    case when 
        parts = lag(parts) over (order by parts) 
            then (lag(non_restricted_empty_bins,1,0) over (partition by parts order by theater) - non_restricted_empty_bins)
    else Open_Repair_Orders - Non_Restricted_Empty_Bins end as Rpr_Order_Consumption
    FROM CX_DB.CX_GSLOBAC_STG.XXBAC_SPM_FILL_RATE_TEST;

Column E is my desired output.

Answers

In your Snowflake query, the issue seems to be with the use of the lag() function within the case statement. The lag() function is used to access the value of a previous row in the result set, but it may not work as intended when used within a window function, especially if the data is not ordered properly.

To achieve the desired behavior similar to the Excel formula, you can use a conditional logic to check if the current row's parts value is the same as the previous row's parts value. If it is, subtract the current row's non_restricted_empty_bins from the previous row's non_restricted_empty_bins; otherwise, subtract the current row's Non_Restricted_Empty_Bins from the current row's Open_Repair_Orders.

Here's how you can modify your query to achieve this:

SELECT 
    THEATER, 
    PARTS, 
    Open_Repair_Orders,
    Non_Restricted_Empty_Bins,
    CASE 
        WHEN parts = lag(parts) over (order by theater) THEN 
            lag(non_restricted_empty_bins) over (order by theater) - non_restricted_empty_bins
        ELSE 
            Open_Repair_Orders - Non_Restricted_Empty_Bins 
    END as Rpr_Order_Consumption
FROM 
    CX_DB.CX_GSLOBAC_STG.XXBAC_SPM_FILL_RATE_TEST
ORDER BY 
    theater;

This query will calculate the Rpr_Order_Consumption based on the conditions you provided and should produce the desired output. Adjust the ORDER BY clause as per your requirement to ensure the correct order of rows.