sql-servercase-whendbo

SSMS (SQL) Not recognizing CASE WHEN... jumps to last ELSE 0


For example's sake, this returns an ORDER NUMBER of 55 and Calc_ORDER_NUMBER_LAG of 55, the Calc_Qty_Changed column doesn't give me a 2, even though they are equal...

USE MfgMetrics

SELECT 
    [ORDER NUMBER], 
    [Calc_ORDER_NUMBER_LAG] = LAG([ORDER NUMBER],1,0) 
                                 OVER (Order By [ORDER NUMBER],[FileDate]), 
                                 --Order Number and File Date 
    [Order_Quantity], 
    [Calc_Order_Quantity_LAG] = LAG([Order_Quantity],1,0) 
                                   OVER (Order By [ORDER NUMBER], [FileDate]),
    [Calc_Qty_Changed] = 
        (CASE 
            WHEN [ORDER NUMBER] = [Calc_ORDER_NUMBER_LAG] THEN 2
            WHEN [ORDER NUMBER] != [Calc_ORDER_NUMBER_LAG] AND 
                        [Order_Quantity] != [Calc_Order_Quantity_LAG] AND 
                        [ACTUAL START DATE] != 0 AND 
                        [FileDate] >= [ACTUAL START DATE] THEN 1
            ELSE 0
        END)
FROM 
    dbo.Table_II

Why would it skip to the ELSE 0 every time, even when the other CASE WHEN conditions are met?


Solution

  • I'm guessing, since I don't know how the data looks like, you should do something like this:

    USE MfgMetrics 
    SELECT [ORDER NUMBER],
    [Calc_ORDER_NUMBER_LAG] = LAG([ORDER NUMBER],1,0) OVER (Order By [ORDER NUMBER],[FileDate]), --Order Number and File Date
    [Order_Quantity],
    [Calc_Order_Quantity_LAG] = LAG([Order_Quantity],1,0) OVER (Order By [ORDER NUMBER], [FileDate]),
    [Calc_Qty_Changed] = (CASE 
        WHEN [ORDER NUMBER]=LAG([ORDER NUMBER],1,0) OVER (Order By [ORDER NUMBER],[FileDate]) THEN 2 
        WHEN [ORDER NUMBER]!=LAG([ORDER NUMBER],1,0) OVER (Order By [ORDER NUMBER],[FileDate]) 
            AND [Order_Quantity] != LAG([Order_Quantity],1,0) OVER (Order By [ORDER NUMBER], [FileDate]) AND [ACTUAL START DATE] != 0 AND [FileDate] >= [ACTUAL START DATE] 
        THEN 1 
        ELSE 0 END) 
    FROM dbo.Table_II