[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Combining Sum and Datediff

Posted on 2005-04-12
3
Medium Priority
?
729 Views
Last Modified: 2006-11-18
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
0
Comment
Question by:polynominal
3 Comments
 
LVL 8

Accepted Solution

by:
nagki earned 300 total points
ID: 13760585
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
 
LVL 43

Assisted Solution

by:TimCottee
TimCottee earned 200 total points
ID: 13760592
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 Comment

by:polynominal
ID: 13760664
Thanks - daft mistake on my part
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question