Select Case

select cam.npart
,sum (case when deta = '9999-12-31' AND
CSTPURORDCONF <'30' then qconf end) as no_eta
,sum (case when deta = current_date - 1 day AND
CSTPURORDCONF ='30' then qconf end) as current_date - 1 day ( THIS DOES NOT WORK °
FROM hsp01.CAMPAIGN_T as cam
left outer join HMR.MPUCOT A
on a.npart = cam.npart
and A.CBUSN='MA'
AND A.DETA > '2009-04-01'
group by cam.npart

sum(case when deta=dateadd(day,-1,cast(current_timestamp as date)) and cstpurordconf='30' then qconf else 0 end)

sum(case when deta=dateadd(day,-1,cast(current_timestamp as date)) and cstpurordconf='30' then qconf else 0 end)

current_date - 1 da_y

sum(case when deta=dateadd(day,-1,cast(current_timestamp as date)) and cstpurordconf='30' then qconf else 0 end) as current_date - 1 day

as yesterday

or

as [current_date - 1 day]