title: Multiple conditions in a CASE statement
body: <p>I want to assess whether certain conditions are met and then return a certain text. I'm using the CASE syntax, but I'm not entirely sure if this is the most practical way to do this. Also because certain conditions have to match additional conditions (the WHEN statement that returns 'Delay'). Even though it looks messy, it does work, except for the last criteria in the 'Delay' statement.</p>
  
  <p>This is my current CASE statement:</p>
  
  <pre><code>    CASE
      WHEN
      (stf.status_id IN ('38','14','13','12','16','18') AND stf.date &gt;= (p.fecha_prevista_entrega + INTERVAL '30 minutes')) 
      OR (stf.status_id IN ('38','14','13','12','16','18') AND stf.date &lt; (os.rider_accepted + INTERVAL '5 minutes'))
      THEN 'Not Paid - Cancelled' 
  
      WHEN
      (stf.status_id IN ('38','14','13','12','16','18') AND stf.date &lt;= (p.fecha_prevista_entrega + INTERVAL '30 minutes')) 
      AND (stf.status_id IN ('38','14','13','12','16','18') AND stf.date &gt;= p.fecha_entrega_repartidor)
      THEN 'Paid - Cancelled'
  
      WHEN
      (u.first_name ILIKE '%LKF%' AND ((EXTRACT(HOUR FROM os.food_delivered - p.fecha_prevista_entrega)*60+EXTRACT(MINUTE FROM os.food_delivered - p.fecha_prevista_entrega)+EXTRACT(SECOND FROM os.food_delivered - p.fecha_prevista_entrega)/60) &gt; '15'))
      OR (u.first_name NOT ILIKE '%LKF%' AND ((EXTRACT(HOUR FROM os.food_delivered - p.fecha_prevista_entrega)*60+EXTRACT(MINUTE FROM os.food_delivered - p.fecha_prevista_entrega)+EXTRACT(SECOND FROM os.food_delivered - p.fecha_prevista_entrega)/60) &gt; '30'))
  
      AND (
      ((od.preorder = '0' AND od.order_comment ILIKE '%' || u.first_name || '%') AND (EXTRACT(HOUR FROM os.rider_assigned - od.date)*60+EXTRACT(MINUTE FROM os.rider_assigned - od.date)+EXTRACT(SECOND FROM os.rider_assigned - od.date)/60) &lt; '15')
  
      OR        
      ((od.preorder = '0' AND od.order_comment NOT ILIKE '%' || u.first_name || '%') AND (EXTRACT(HOUR FROM os.rider_assigned - od.date)*60+EXTRACT(MINUTE FROM os.rider_assigned - od.date)+EXTRACT(SECOND FROM os.rider_assigned - od.date)/60) &lt; '20')
  
      OR
      (od.preorder = '1' AND (EXTRACT(HOUR FROM p.fecha_prevista_entrega - os.rider_assigned)*60+EXTRACT(MINUTE FROM p.fecha_prevista_entrega - os.rider_assigned)+EXTRACT(SECOND FROM p.fecha_prevista_entrega - os.rider_assigned)/60) &gt; '30')
  
      OR
      ((EXTRACT(HOUR FROM os.food_delivered - p.fecha_prevista_entrega)*60+EXTRACT(MINUTE FROM os.food_delivered - p.fecha_prevista_entrega)+EXTRACT(SECOND FROM os.food_delivered - p.fecha_prevista_entrega)/60) &gt; '15')
  
      OR
      ((os.rider_at_restaurant &gt;= (p.fecha_entrega_repartidor + INTERVAL '3 minutes')) AND ((EXTRACT(HOUR FROM os.food_picked_up - p.fecha_entrega_repartidor)*60+EXTRACT(MINUTE FROM os.food_picked_up - p.fecha_entrega_repartidor)+EXTRACT(SECOND FROM os.food_picked_up - p.fecha_entrega_repartidor)/60) &lt; '15'))
  
  
      )
      THEN 'Delay'
  
      ELSE 'None' END AS payment_adj
  </code></pre>
  
  <p>This part of the statement does not work:</p>
  
  <pre><code>    OR
      ((os.rider_at_restaurant &gt;= (p.fecha_entrega_repartidor + INTERVAL '3 minutes')) AND ((EXTRACT(HOUR FROM os.food_picked_up - p.fecha_entrega_repartidor)*60+EXTRACT(MINUTE FROM os.food_picked_up - p.fecha_entrega_repartidor)+EXTRACT(SECOND FROM os.food_picked_up - p.fecha_entrega_repartidor)/60) &lt; '15'))
  </code></pre>
  
  <p>I've crosschecked several results that show 'Delay', but should not show it because they don't match this condition.</p>
  
  <p>Here's the full query</p>
  
  <pre><code>WITH order_steps AS                         
  (                       
      SELECT pedido_id,                       
      MAX(CASE WHEN situacion  = 0 THEN created_at END) AS rider_assigned,                        
      MAX(CASE WHEN situacion  = 1 THEN created_at END) AS rider_viewed,                      
      MAX(CASE WHEN situacion  = 2 THEN created_at END) AS rider_accepted,                        
      MAX(CASE WHEN situacion  = 3 THEN created_at END) AS         rider_at_restaurant,                       
      MAX(CASE WHEN situacion  = 4 THEN created_at END) AS food_picked_up,                        
      MAX(CASE WHEN situacion  = 5 THEN created_at END) AS rider_at_customer,                     
      MAX(CASE WHEN situacion  = 6 THEN created_at END) AS food_delivered,                        
      MAX(CASE WHEN situacion  = 10 THEN created_at END) AS unknown_status                        
      FROM dwh.tracking_motero t                      
      GROUP BY 1                      
      ORDER BY 1,2                        
  ),                      
      assigned_riders AS (                        
      WITH rider_assignments AS (                     
      SELECT mp.pedido_id, mp.motero_id, mp.created_at, ROW_NUMBER() OVER (PARTITION BY mp.pedido_id ORDER BY mp.created_at DESC) AS last_assignments                     
      FROM dwh.motero_pedido mp                       
      )                       
      SELECT pedido_id, motero_id FROM rider_assignments                      
      WHERE last_assignments = 1                      
      )                       
  
      SELECT 
      CASE
      WHEN
      (stf.status_id IN ('38','14','13','12','16','18') AND stf.date &gt;= (p.fecha_prevista_entrega + INTERVAL '30 minutes')) 
      OR (stf.status_id IN ('38','14','13','12','16','18') AND stf.date &lt; (os.rider_accepted + INTERVAL '5 minutes'))
      THEN 'Not Paid - Cancelled' 
  
      WHEN
      (stf.status_id IN ('38','14','13','12','16','18') AND stf.date &lt;= (p.fecha_prevista_entrega + INTERVAL '30 minutes')) 
      AND (stf.status_id IN ('38','14','13','12','16','18') AND stf.date &gt;= p.fecha_entrega_repartidor)
      THEN 'Paid - Cancelled'
  
      WHEN
      (u.first_name ILIKE '%LKF%' AND ((EXTRACT(HOUR FROM os.food_delivered - p.fecha_prevista_entrega)*60+EXTRACT(MINUTE FROM os.food_delivered - p.fecha_prevista_entrega)+EXTRACT(SECOND FROM os.food_delivered - p.fecha_prevista_entrega)/60) &gt; '15'))
      OR (u.first_name NOT ILIKE '%LKF%' AND ((EXTRACT(HOUR FROM os.food_delivered - p.fecha_prevista_entrega)*60+EXTRACT(MINUTE FROM os.food_delivered - p.fecha_prevista_entrega)+EXTRACT(SECOND FROM os.food_delivered - p.fecha_prevista_entrega)/60) &gt; '30'))
  
      AND (
      ((od.preorder = '0' AND od.order_comment ILIKE '%' || u.first_name || '%') AND (EXTRACT(HOUR FROM os.rider_assigned - od.date)*60+EXTRACT(MINUTE FROM os.rider_assigned - od.date)+EXTRACT(SECOND FROM os.rider_assigned - od.date)/60) &lt; '15')
  
      OR        
      ((od.preorder = '0' AND od.order_comment NOT ILIKE '%' || u.first_name || '%') AND (EXTRACT(HOUR FROM os.rider_assigned - od.date)*60+EXTRACT(MINUTE FROM os.rider_assigned - od.date)+EXTRACT(SECOND FROM os.rider_assigned - od.date)/60) &lt; '20')
  
      OR
      (od.preorder = '1' AND (EXTRACT(HOUR FROM p.fecha_prevista_entrega - os.rider_assigned)*60+EXTRACT(MINUTE FROM p.fecha_prevista_entrega - os.rider_assigned)+EXTRACT(SECOND FROM p.fecha_prevista_entrega - os.rider_assigned)/60) &gt; '30')
  
      OR
      ((EXTRACT(HOUR FROM os.food_delivered - p.fecha_prevista_entrega)*60+EXTRACT(MINUTE FROM os.food_delivered - p.fecha_prevista_entrega)+EXTRACT(SECOND FROM os.food_delivered - p.fecha_prevista_entrega)/60) &gt; '15')
  
      OR
      ((os.rider_at_restaurant &gt;= (p.fecha_entrega_repartidor + INTERVAL '3 minutes')) AND ((EXTRACT(HOUR FROM os.food_picked_up - p.fecha_entrega_repartidor)*60+EXTRACT(MINUTE FROM os.food_picked_up - p.fecha_entrega_repartidor)+EXTRACT(SECOND FROM os.food_picked_up - p.fecha_entrega_repartidor)/60) &lt; '15'))
  
  
      )
      THEN 'Delay'
  
  
  
      ELSE 'None' END AS payment_adj,
      p.id AS urban_ninja_id,                         
      o.order_fp_code AS order_code,                      
      p.total AS order_amount,                        
      p.paymenttype_name,                     
      u.first_name AS rider_code,                     
      p.direccion AS delivery_address,                        
      p.fecha_entrega_repartidor AS expected_pick_up_time,                        
      p.fecha_prevista_entrega AS expected_delivery_time,                     
      od.date AS order_creation_date,                     
      os.rider_assigned,                      
      os.rider_viewed,                        
      os.rider_accepted,                      
      os.rider_at_restaurant,                     
      os.food_picked_up,                      
      os.rider_at_customer,                       
      os.food_delivered,                      
      os.unknown_status,                      
      st.id AS status_id,                     
      st.code AS status_code,                     
      st.title AS status,                     
      ve.title AS vendor,                     
      EXTRACT(HOUR FROM os.rider_assigned-od.date)*60+EXTRACT (MINUTE FROM os.rider_assigned-od.date)+EXTRACT (SECOND FROM os.rider_assigned-od.date)/60 AS dispatching_time,                     
      EXTRACT(HOUR FROM os.rider_viewed-os.rider_assigned)*60+EXTRACT (MINUTE FROM os.rider_viewed-os.rider_assigned)+EXTRACT (SECOND FROM os.rider_viewed-os.rider_assigned)/60 AS rider_reaction_time,                      
      EXTRACT(HOUR FROM os.rider_accepted-os.rider_viewed)*60+EXTRACT (MINUTE FROM os.rider_accepted-os.rider_viewed)+EXTRACT (SECOND FROM os.rider_accepted-os.rider_viewed)/60 AS rider_acceptance_time,                        
      EXTRACT(HOUR FROM os.rider_at_restaurant-os.rider_accepted)*60+EXTRACT (MINUTE FROM os.rider_at_restaurant-os.rider_accepted)+EXTRACT (SECOND FROM os.rider_at_restaurant-os.rider_accepted)/60 AS rider_driving_to_restaurant_time,                        
      EXTRACT(HOUR FROM os.food_picked_up-os.rider_at_restaurant)*60+EXTRACT (MINUTE FROM os.food_picked_up-os.rider_at_restaurant)+EXTRACT (SECOND FROM os.food_picked_up-os.rider_at_restaurant)/60 AS rider_in_restaurant_time,                        
      EXTRACT(HOUR FROM os.rider_at_customer-os.food_picked_up)*60+EXTRACT (MINUTE FROM os.rider_at_customer-os.food_picked_up)+EXTRACT (SECOND FROM os.rider_at_customer-os.food_picked_up)/60 AS rider_driving_to_customer_time,                        
      EXTRACT(HOUR FROM os.food_delivered-os.rider_at_customer)*60+EXTRACT (MINUTE FROM os.food_delivered-os.rider_at_customer)+EXTRACT (SECOND FROM os.food_delivered-os.rider_at_customer)/60 AS rider_at_customer_time,                        
      EXTRACT (HOUR FROM os.food_delivered-od.date)*60 + EXTRACT (MINUTE FROM os.food_delivered-od.date) + EXTRACT (SECOND FROM os.food_delivered-od.date)/60 AS delivery_time,                       
      od.order_comment,                       
      od.preorder                     
      FROM dwh.pedido p                       
      LEFT JOIN dwh.order_fp o ON p.id = o.pedido_id                      
      LEFT JOIN order_steps os ON os.pedido_id = p.id                     
      LEFT JOIN assigned_riders r ON r.pedido_id = p.id                       
      LEFT JOIN dwh.moteros m ON m.id = r.motero_id                       
      LEFT JOIN dwh.sf_guard_user u ON u.id = m.sf_guard_user_id                      
      INNER JOIN dwh."Orders" od ON od.id = o.order_fp_id                     
      INNER JOIN dwh."Status" st ON st.id = od.status_id
      INNER JOIN dwh."Statusflows" stf ON stf.order_id = od.id
      INNER JOIN dwh."Vendors" ve ON od.vendor_id = ve.id                     
      WHERE u.first_name NOT IN ('TEST RIDER','Jan')                      
      and os.rider_accepted between (current_date-30) and (current_date-1)        
     GROUP BY p.id, stf.status_id, stf.date, os.rider_accepted, u.first_name, os.food_delivered, od.preorder, od.order_comment, os.rider_assigned, od.date, os.food_picked_up, os.rider_at_restaurant, o.order_fp_code, os.rider_viewed, os.rider_at_customer, os.unknown_status, st.id, ve.title
     LIMIT 100;                                           
  </code></pre>