Admin

I have the following query:

select a.file_job, Cntr=count(cont_num), CntrOut=count(date_out), CntrIn=count(date_in), Status=''
from cargodetailcontainer a join cargodetail b on a.file_job=b.file_job
group by a.file_job
order by a.file_job

If all three column value is equle (Cntr=count(cont_num), CntrOut=count(date_out), CntrIn=count(date_in)) then on status (last cloumn) I have to show "Closed" else "Open"



Solution:

select a.file_job, count(cont_num) CNTR, count(date_out) CNTR_OUT, count(date_in) CNTR_IN, When (CNTR = CNTR_OUT AND CNTR = CNTR_IN then "Closed" ELSE "Open" END) Status
from cargodetailcontainer a join cargodetail b on a.file_job=b.file_job
group by a.file_job
order by a.file_job


and

select a.file_job, Cntr=count(cont_num), CntrOut=count(date_out), CntrIn=count(date_in),
CASE count(cont_num) WHEN count(date_out) | count(date_in) THEN 'Closed' ELSE 'Open' END Status
from cargodetailcontainer a join cargodetail b on a.file_job=b.file_job
group by a.file_job
order by a.file_job