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