Case Statement

Question

Case Statement

yrstruly

Hall of Fame

Points: 3440
More actions
January 4, 2022 at 10:45 am

#3969661

I am having an error with this code:

select customer_name, order_id,
(case when sum(status = 'delivered') = count(*)
then 'completed'
when sum(status = 'delivered') > 0
then 'in progress'
when sum(status = 'submitted') > 0
then 'awaiting progress'
else 'awaiting submission'
end) as status
from customer_order
group by customer_name;

error = Msg 156, Level 15, State 1, Server dbrank-tsql, Line 2
Incorrect syntax near the keyword 'when'

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246740 More actions · Answer 1

Try this

SELECT customer_name
      ,order_id
      ,status = (CASE
                     WHEN SUM(IIF(status = 'delivered', 1, 0)) = COUNT(*) THEN
                         'completed'
                     WHEN SUM(IIF(status = 'delivered', 1, 0)) > 0 THEN
                         'in progress'
                     WHEN SUM(IIF(status = 'submitted', 1, 0)) > 0 THEN
                         'awaiting progress'
                     ELSE
                         'awaiting submission'
                 END
                )
FROM customer_order
GROUP BY customer_name;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.