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]