Combining Datediff and Count

I have a query where I need to combine count with datediff, here what I have so far

SELECT     COUNT(look.jobid) AS Complete, SUM(job.autval) AS Value, COUNT(datediff([d], start.complete, job.rbd) >= 0 AS RBD, contract.clientcontractno

I cant get this to work as there is an error with the syntax, does anyone know the correct syntax

Thanks
polynominalAsked:
Who is Participating?
 
roshkmCommented:
Give this a try

SELECT     COUNT(complete.jobid) AS Complete, SUM(job.autval) AS Value, COUNT(CASE WHEN datediff([d], complete.engcomplete, job.rbd) >= 0 THEN 1 ELSE NULL END) AS RBD, contract.clientcontractno
FROM         job INNER JOIN
                      complete ON job.jobid = complete.jobid INNER JOIN
                      contract ON job.contractid = contract.contractid
WHERE     (job.contractid IN (5, 6, 7, 12, 13, 14)) AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.engcomplete))) >= CONVERT(DATETIME,
                      '2005-03-01 00:00:00', 102)) AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.engcomplete))) <= CONVERT(DATETIME,
                      '2005-03-31 00:00:00', 102))
GROUP BY contract.clientcontractno

Regards
RKM
0
 
nagkiCommented:
In that count of datediff one right paranthesis is missing..
try this:
SELECT     COUNT(look.jobid) AS Complete, SUM(job.autval) AS Value, COUNT(datediff([d], start.complete, job.rbd)) >= 0 AS RBD, contract.clientcontractno

Nagki
0
 
NeoTeqCommented:
Well... For one, you forgot a )

Also, you forgot your tables it seems... You'll need a from clause. I don't know your table structure, but I imagine it'd be something like:

SELECT     COUNT(look.jobid) AS Complete, SUM(job.autval) AS Value, COUNT(datediff([d], start.complete, job.rbd) >= 0) AS RBD, contract.clientcontractno
FROM look inner join job on look.jobid = job.jobid inner join start on ????
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
auke_tCommented:
--I think this is what you are trying to do:
SELECT
    COUNT(look.jobid) AS Complete,
    SUM(job.autval) AS Value,
    COUNT(when datediff([d], start.complete, job.rbd) >= 0 then 1 else null end) AS RBD,
    contract.clientcontractno
FROM
0
 
roshkmCommented:
correction to what Auke has suggested:

SELECT
    COUNT(look.jobid) AS Complete,
    SUM(job.autval) AS Value,
    COUNT(case when datediff([d], start.complete, job.rbd) >= 0 then 1 else null end) AS RBD,
    contract.clientcontractno
FROM
.........

Regards,
RKM
0
 
polynominalAuthor Commented:
auke that is what I am looking for, however I get the following error
Error in SELECT clause: expression near 'WHEN'.
Missing FROM clause.
Error in SELECT clause: expression near ','.
Unable to parse query text.

My full query is

SELECT     COUNT(complete.jobid) AS Complete, SUM(job.autval) AS Value, COUNT(WHEN datediff([d], complete.engcomplete, job.rbd) >= 0 THEN 1 ELSE NULL END) AS RBD, contract.clientcontractno
FROM         job INNER JOIN
                      complete ON job.jobid = complete.jobid INNER JOIN
                      contract ON job.contractid = contract.contractid
WHERE     (job.contractid IN (5, 6, 7, 12, 13, 14)) AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.engcomplete))) >= CONVERT(DATETIME,
                      '2005-03-01 00:00:00', 102)) AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.engcomplete))) <= CONVERT(DATETIME,
                      '2005-03-31 00:00:00', 102))
GROUP BY contract.clientcontractno
0
 
auke_tCommented:
Roshkm is right, I forgot to add 'case'.
0
 
NeoTeqCommented:
Use the select roshkm posted:

SELECT     COUNT(complete.jobid) AS Complete, SUM(job.autval) AS Value, COUNT(case when datediff([d], start.complete, job.rbd) >= 0 then 1 else null end) AS RBD, contract.clientcontractno
FROM         job INNER JOIN
                      complete ON job.jobid = complete.jobid INNER JOIN
                      contract ON job.contractid = contract.contractid
WHERE     (job.contractid IN (5, 6, 7, 12, 13, 14)) AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.engcomplete))) >= CONVERT(DATETIME,
                      '2005-03-01 00:00:00', 102)) AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.engcomplete))) <= CONVERT(DATETIME,
                      '2005-03-31 00:00:00', 102))
GROUP BY contract.clientcontractno
0
 
roshkmCommented:
I only assisted you Auke..  :)

Regards,
RKM
0
 
auke_tCommented:
Hey, what's this!

I think I made a valuable contribution to the solution, but you spit the points between roshkm and roshkm.

Is this a mistake or do you want to punish me? ;-)
0
 
polynominalAuthor Commented:
Sorry made a mistake there, not on purpose

Can I amend this in anyway
0
 
NeoTeqCommented:
You can post a question in Community Support to reopen this question. Then, you can accept the right comments as answer.
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.