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
polynominalAsked:
Who is Participating?
 
nagkiConnect With a Mentor 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
 
TimCotteeConnect With a Mentor Head of Software ServicesCommented:
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
 
polynominalAuthor 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.

All Courses

From novice to tech pro — start learning today.