Analytics
Highlighted

Using the sum(if (date - another_date < 5))

I want to get a count of the numeric difference of the dates less than 5. I have tried everything?

select sum(if(ddobj - current_date < 5))
from WFA_PROV_CMB
where CTR in ('STC')
and STAT in ('IE','IX','HI','P','PA','PC','PX','PW','IK','I')
and scrcomp between to_date ('01/05/09', 'mm/dd/yy') AND to_date ('01/11/09', 'mm/dd/yy')
and act in ('A','D','R','RN')
and substr(ckt,8,2) in ('OC','OM','OT') and substr(ckt,12,1) NOT = 'C' and substr(trk,3,1) NOT = 'D' and proc in ('CAC','GAC','I48','LMC','NFC','SFC','TNC')
2 REPLIES 2

Re: Using the sum(if (date - another_date < 5))

I get a Syntax error: expected something between '(' and the 'if' keyword

Re: Using the sum(if (date - another_date < 5))

select sum(case when ddobj - current_date < 5 then 1 else 0 end)
from WFA_PROV_CMB
where CTR in ('STC')
and STAT in ('IE','IX','HI','P','PA','PC','PX','PW','IK','I')
and scrcomp between date'2009-01-05' and date'2009-01-11'
and act in ('A','D','R','RN')
and substr(ckt,8,2) in ('OC','OM','OT') and substr(ckt,12,1) NOT = 'C' and substr(trk,3,1) NOT = 'D' and proc in ('CAC','GAC','I48','LMC','NFC','SFC','TNC')