如何从SQL中的两个不同列中确定状态

Question

我试图确定查询中的“完整”或“不完整”值，但我需要引用两个不同的列来确定。

例如-我有一个货件有4个事件。在数据库中，我有以下列。

“cs_event_count”与“cs_completed”

Event count告诉我事件总数,Completed告诉我这些事件中有多少已经完成。

我希望我的查询显示如下内容

如果“Event Count”值等于“Completed”值，则为“Complete”；如果“Event Count”与“Completed”值不匹配，则为“Complete”

我还从其他表中引入了数据。这是我的问题

下面是我的查询的示例

  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

根据问题中包含的代码，您是否尝试使用case表达式？

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