Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Combining Datediff and Count

Posted on 2005-04-11
Medium Priority
1,064 Views
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
0
Question by:polynominal
• 3
• 3
• 3
• +2

LVL 8

Expert Comment

ID: 13751285
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

LVL 10

Expert Comment

ID: 13751291
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

LVL 9

Expert Comment

ID: 13751303
--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

LVL 4

Assisted Solution

roshkm earned 500 total points
ID: 13751326
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

Author Comment

ID: 13751327
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

LVL 4

Accepted Solution

roshkm earned 500 total points
ID: 13751341
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

LVL 9

Expert Comment

ID: 13751342
Roshkm is right, I forgot to add 'case'.
0

LVL 10

Expert Comment

ID: 13751344
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

LVL 4

Expert Comment

ID: 13751393
I only assisted you Auke..  :)

Regards,
RKM
0

LVL 9

Expert Comment

ID: 13756511
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

Author Comment

ID: 13756553
Sorry made a mistake there, not on purpose

Can I amend this in anyway
0

LVL 10

Expert Comment

ID: 13756623
You can post a question in Community Support to reopen this question. Then, you can accept the right comments as answer.
0

## Featured Post

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month11 days, 20 hours left to enroll