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
12
Medium Priority
?
1,064 Views
Last Modified: 2012-08-13
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
Comment
Question by:polynominal
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 8

Expert Comment

by:nagki
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

by:NeoTeq
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

by:auke_t
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Assisted Solution

by:roshkm
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

by:polynominal
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

by:
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

by:auke_t
ID: 13751342
Roshkm is right, I forgot to add 'case'.
0
 
LVL 10

Expert Comment

by:NeoTeq
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

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

Regards,
RKM
0
 
LVL 9

Expert Comment

by:auke_t
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

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

Can I amend this in anyway
0
 
LVL 10

Expert Comment

by:NeoTeq
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

564 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