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

Question

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?

Answer 1

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

Answer 2

You should re-write LAG() functions in your CASE statement.

create table table_II([ORDER NUMBER] int, [Order_Quantity] int, [FileDate] date);
insert into table_II values
(1, 10, '20180101'),
(2, 20, '20180102'),
(2, 30, '20180103');
GO

3 rows affected

SELECT 
    [ORDER NUMBER], 
    [Calc_ORDER_NUMBER_LAG] = LAG([ORDER NUMBER]) OVER (Order By [ORDER NUMBER], [FileDate]), 
    [Order_Quantity], 
    [Calc_Order_Quantity_LAG] = LAG([Order_Quantity]) OVER (Order By [ORDER NUMBER], [FileDate]),
    [Calc_Qty_Changed] = 
        (CASE 
            WHEN [ORDER NUMBER] = LAG([ORDER NUMBER]) OVER (Order By [ORDER NUMBER], [FileDate]) 
                 THEN 2
            WHEN [ORDER NUMBER] != LAG([ORDER NUMBER]) OVER (Order By [ORDER NUMBER], [FileDate]) 
                 AND [Order_Quantity] != LAG([Order_Quantity]) 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
GO

ORDER NUMBER | Calc_ORDER_NUMBER_LAG | Order_Quantity | Calc_Order_Quantity_LAG | Calc_Qty_Changed
-----------: | --------------------: | -------------: | ----------------------: | ---------------:
           1 |                  null |             10 |                    null |                0
           2 |                     1 |             20 |                      10 |                1
           2 |                     2 |             30 |                      20 |                2

dbfiddle here