How to determine a status from two different columns in SQL

Question

I am trying to determine a "Complete" or "Incomplete" value in my query but I need to reference two different columns to determine.

For example - I have a shipment that has 4 events. In the database, I have the following columns.

'cs_event_count' and 'cs_completed'

Event count tells me the total # of events and Completed tells me how many of those events have been completed.

I want my query to say something like the following

If 'Event count' value equals 'Completed' value then "COMPLETE" If 'Event count' does not match 'Completed' value then "INCOMPLETE"

I am also bringing in data from other tables. Here is my query

Here is a sample of my query

  select dba.disp_ship.ds_id, dba.disp_ship.ds_origin_id, dba.disp_ship.ds_findest_id, dba.disp_ship.ds_billto_id, dba.disp_ship.ds_bill_charge,
dba.disp_ship.ds_status, dba.disp_ship.ds_bill_date, dba.disp_ship.ds_ship_date, dba.disp_ship.ds_ship_type, dba.disp_ship.movecode, 
dba.companies.co_id, dba.companies.co_name, dba.current_shipments.cs_id, dba.current_shipments.cs_event_count, dba.current_shipments.cs_routed, dba.current_shipments.cs_assigned, dba.current_shipments.cs_completed,
(CASE ds_status WHEN 'A' THEN 'TEMPLATE'
WHEN 'C' THEN 'CANCELLED'
WHEN 'D' THEN 'DECLINED'
WHEN 'E' THEN 'QUOTED'
WHEN 'F' THEN 'OFFERED' 
WHEN 'H' THEN 'PENDING'
WHEN 'K' THEN 'OPEN'
WHEN 'N' THEN 'AUTHORIZED'
WHEN 'Q' THEN 'AUDIT REQUIRED'
WHEN 'T' THEN 'AUDITED'
WHEN 'W' THEN 'BILLED' 
END) AS 'BILLING STATUS',
(CASE ds_ship_type WHEN '2201' THEN 'MONTREAL'
WHEN '2202' THEN 'DRYVAN'
WHEN '2203' THEN 'BROKERAGE'
WHEN '2204' THEN 'OLD BROKERAGE (NO GOOD)'
WHEN '2205' THEN 'LIFTING'
WHEN '2206' THEN 'WAREHOUSE'
END) AS 'DIVISION'
from dba.disp_ship
inner join dba.companies ON dba.disp_ship.ds_billto_id=dba.companies.co_id
inner join dba.current_shipments ON dba.disp_ship.ds_id=DBA.current_shipments.cs_id 

Answer 1

Did you try using a CASE expression, as per the code included in your question?

CASE WHEN dba.current_shipments.cs_event_count = dba.current_shipments.cs_completed
     THEN   'COMPLETE'
     ELSE 'INCOMPLETE'
END
  AS Status