polynominal
asked on
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
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
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 ????
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 ????
--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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Roshkm is right, I forgot to add 'case'.
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
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
I only assisted you Auke.. :)
Regards,
RKM
Regards,
RKM
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? ;-)
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? ;-)
ASKER
Sorry made a mistake there, not on purpose
Can I amend this in anyway
Can I amend this in anyway
You can post a question in Community Support to reopen this question. Then, you can accept the right comments as answer.
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