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?

I have a hard time believing the query as written actually runs. It should throw an Invalid Column Name error when it encounters the column alias in the calculation. And that's why the proposed answers work, by the way. — Eric Brandt, Mar 01 '18 at 21:33

score 2 · Answer 1 · answered Mar 01 '18 at 19:55

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

Hi McNets! It looks like your solution is pretty similar to the one above, which worked. I appreciate you taking the time to help. I also had never seen dbfiddle before which is awesome! Thank you again!! — mitchmitch24, Mar 02 '18 at 13:30

score 1 · Accepted Answer · answered Mar 01 '18 at 20:00

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

Hi user3532232! Thank you so much! That worked like a charm. Can you help explain what adding the OVER (Order by...) within each part of the CASE WHEN pieces achieve? I am still fairly new to SQL and want to better understand when people take the time to reach out and help! Thank you again!! — mitchmitch24, Mar 02 '18 at 13:28
you calculate the [calc_ORDER_NUMBER_LAG] in the second column then tries to use that value in the last column. This can't be done, you need to do the calculation again in the last column. Alternavly you could have made an outerquery using the calculated value. — user3532232, Mar 02 '18 at 13:41

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

2 Answers2