# Combining Sum and Datediff

I have written a query below, but cannot get sum to work, as it keeps giving me the wrong syntax, any ideas?

SELECT     company.companyname, SUM(CASE WHEN datediff([d], cssdata.received, getdate()) < 30) as Less
FROM         contract INNER JOIN
job ON contract.contractid = job.contractid INNER JOIN
cssdata ON job.jobid = cssdata.jobid INNER JOIN
allocation ON job.jobid = allocation.jobid INNER JOIN
vw_job_lastliveallocation ON allocation.allocationid = vw_job_lastliveallocation.lastallocation INNER JOIN
company ON allocation.companyid = company.companyid

Thanks

Mark
###### Who is Participating?

Commented:
u didn't end ur case statment..
try this:

SELECT     company.companyname, SUM(CASE WHEN datediff([d], cssdata.received, getdate()) < 30 then 1 else 0 end) as Less
FROM         contract INNER JOIN
job ON contract.contractid = job.contractid INNER JOIN
cssdata ON job.jobid = cssdata.jobid INNER JOIN
allocation ON job.jobid = allocation.jobid INNER JOIN
vw_job_lastliveallocation ON allocation.allocationid = vw_job_lastliveallocation.lastallocation INNER JOIN
company ON allocation.companyid = company.companyid

0

Hi polynominal,

I think you mean:

SELECT     company.companyname, SUM(CASE WHEN datediff([d], cssdata.received, getdate()) < 30 Then 1 Else 0 End) as Less
FROM         contract INNER JOIN
job ON contract.contractid = job.contractid INNER JOIN
cssdata ON job.jobid = cssdata.jobid INNER JOIN
allocation ON job.jobid = allocation.jobid INNER JOIN
vw_job_lastliveallocation ON allocation.allocationid = vw_job_lastliveallocation.lastallocation INNER JOIN
company ON allocation.companyid = company.companyid

You didn't have any values to SUM based on the case statement.

Tim Cottee
0

Author Commented:
Thanks - daft mistake on my part
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.