August 12, 2013 at 2:28 pm
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
August 12, 2013 at 2:50 pm
I see two CASE statements, but only one END.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply