Case Statement

• yrstruly

  Hall of Fame

  Points: 3475

  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'

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  January 4, 2022 at 10:53 am

  #3969664

  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;

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin