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.
| Author |
Topic |
|
Salnick4
Starting Member
19 Posts |
 Posted - 2005-06-28 : 13:28:11
|
I am attempting to write this SQL SELECT statement that uses 3 CASE statements...the results I receive is providing duplicate names but with different data...(I will explain)Below is my SQL Statement:Select (EMPLOYEE_NME) AS [Employee Name], (CASE WHEN ACTION_TAKEN = 'Follow-up' THEN COUNT(ACTION_TAKEN) ELSE '0' END) AS [Follow-up Referrals], (CASE WHEN ACTION_TAKEN = 'Warm Transfer' THEN COUNT(ACTION_TAKEN) ELSE '0' END) AS [Warm Transfer Referrals] FROM REFERRAL_MASTER WHERE MANAGER_ID ='30000002' AND CUR_DATE BETWEEN '6/25/05 8:26:48 AM' AND '6/27/05 1:49:57 PM' GROUP BY EMPLOYEE_NME, ACTION_TAKEN Below is a sample of the table data:CURDATE | EMPLOYEE_NME | MANAGER_ID | ACTION_TAKEN6/22/05 4:43:40 PM | Flintstone,Fred | 30000002 | Follow-up6/22/05 4:32:40 PM | Flintstone,Fred | 30000002 | Warm Transfer6/22/05 4:43:40 AM | Flintstone,Fred | 30000002 | Follow-up6/23/05 1:43:40 PM | Jetson,George| 30000002 | Warm Transfer6/23/05 6:43:40 PM | Jetson,George| 30000002 | Warm Transfer6/23/05 4:01:40 PM | Jetson,George| 30000002 | Follow-upWhen I run the above SELECT statement I receive the following results:Flintstone,Fred |____2_____|__________0_______|Flintstone,Fred |____0_____|__________1_______|Jetson,George |____5_____|__________0_______|Jetson,George |____0_____|__________3_______|What my desired results should be is the following:Flintstone,Fred |____2_____|__________1_______|Jetson,George |____5_____|__________3_______|I thought that if I did the GROUP BY on the EMPLOYEE_NME , then it would work...but it is not...If I take the ACTION_TAKEN from the GROUP BY, then I get an error saying that the ACTION_TAKEN needs to be in either the aggregate function or group by clause...Any ideas??? |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-06-28 : 13:55:32
|
you were close... Select [Employee Name] = EMPLOYEE_NME, [Follow-up Referrals] = sum(CASE WHEN ACTION_TAKEN = 'Follow-up' THEN 1 ELSE 0 END), [Warm Transfer Referrals] = sum(CASE WHEN ACTION_TAKEN = 'Warm Transfer' THEN 1 ELSE 0 END)FROM REFERRAL_MASTER WHERE MANAGER_ID ='30000002' AND CUR_DATE BETWEEN '6/25/05 8:26:48 AM' AND '6/27/05 1:49:57 PM' GROUP BY EMPLOYEE_NME Corey Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."  |
 |
|
|
Salnick4
Starting Member
19 Posts |
Posted - 2005-06-28 : 14:49:32
|
| Thank you for your reply...When I ran your revised query through the Query Analyzer. I did not get any data back...any ideas why? |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-06-28 : 14:52:02
|
| did you get an error? This is MS SQL Server, right? Did you try changing the criteria?Thanks!CoreyCo-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now." |
|
|
|
Salnick4
Starting Member
19 Posts |
Posted - 2005-06-28 : 15:14:53
|
That was the problem....I forgot that I had a fake ID in the one field...THANK YOU VERY MUCH!!!!!!!HAVE A GREAT DAY! |
|
|
|
|
|
|
|
|
Group BY Clause HELP!!