Count help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count help

Author

Topic

lbeese
Starting Member

24 Posts

Posted - 2009-06-08 : 16:53:06

Hi,

I have written the following query to get a datediff on authorization_date and referral_request_date, creating the column alias of "turnaround". I now want to do a count on "turnaround" where the datediff is 0. I've tried many different things but keep getting errors. Can someone please assist? Thanks.

select authorization_number, datediff(day, authorization_date, referral_request_date) as turnaround
from ccmsdba.event_referral r
join ccmsdba.ccms_all_users u
on u.ccms_id = r.event_manager
where r.event_status = 'N'
and r.authorization_date between '2009-01-01 00:00:00' and '2009-01-31 00:00:00'
and u.ccms_user_company_name = 'Generic Company'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 18:19:09

sum(case datediff(day, authorization_date, referral_request_date) when 0 then 1 else 0 end) as turnaround

E 12°55'05.63"
N 56°04'39.26"

lbeese
Starting Member

24 Posts

Posted - 2009-06-09 : 10:00:36

Thanks, unfortunately I am receiving the error "Incorrect syntax near the keyword 'case'. " with this statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-09 : 12:05:39

show your full query please

lbeese
Starting Member

24 Posts

Posted - 2009-06-11 : 13:37:38

I finally got this to work. Dumbness on my part. Thanks for the help. However, I am taking it one step further and wanting to do counts on the datediff if the datediff = 1 or 2. My query is running fine, and returning the count if the value is 0, but is returning 0 in the Oneday and Twoday columns. I know I have some one day and 2 day datediffs.

select
sum(case datediff(day, r.authorization_date, r.referral_request_date) when 0 then 1 else 0 end) as Sameday,
sum(case datediff(day, r.authorization_date, r.referral_request_date) when 1 then 1 else 0 end) as Oneday,
sum(case datediff(day, r.authorization_date, r.referral_request_date) when 2 then 1 else 0 end) as Twoday
from ccmsdba.event_referral r

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 13:40:53

what does this give you?

select datediff(day, r.authorization_date, r.referral_request_date),count(*)
from ccmsdba.event_referral r
group by datediff(day, r.authorization_date, r.referral_request_date)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources