I need to create a field that looks at calculation based on the client. My code below needs to apply extra Item field if the client name is 1 or the other:
SELECT [CLIENT], [ORDERNUM], [CARRIER], Count([LINE]) as [LINE COUNT], Sum([QTY]) As [ITEM COUNT], CASE WHEN (Sum([QTY])>3 AND [CLIENT]='CLIENT01' THEN Sum([QTY])-3)
ELSE CASE WHEN (Sum([QTY])>4 AND [CLIENT]='CLIENT02' THEN Sum([QTY])-4) ELSE 0 END AS [EXTRA ITEMS] FROM SQLQRY1
GROUP BY [CLIENT], [ORDERNUM], [CARRIER]
ORDER BY [ORDERNUM]
I feel that I am missing something because I receive a syntax error near 'EXTRA ITEMS'.
Can someone review and provide insight into what I am overlooking?
Thank you-:-P