CASE STATEMENT HELP???

Question

Post reply

CASE STATEMENT HELP???

Salvatore Conti Jr.

Ten Centuries

Points: 1155
More actions
June 28, 2005 at 6:53 am

#164765

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
==============================================================
The table that I am queryiung against would have users who have both "Follow-up" & "Warm Transfer" entries...so logically I thought that I would get results grouped by the Users so that in one row I would see the total # of "Follow-up" entries next to the total # of "Warm Transfer" entries...
Instead I get the user appearing twice...one row for "Follow-up" and the 2nd row for "Warm Transfer"...
Any help would be appreciated...

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Salvatore Conti Jr. Ten Centuries Points: 1155 More actions · Answer 1

Is my issue related to how my SQL statement is constructed??

Or is it something else??

If anyone can assist me, that would be great!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Can you post some sample data, the current results and the expected results. It will be hard to help you without that.

Salvatore Conti Jr. Ten Centuries Points: 1155 More actions · Answer 3

Thank you for responding....below is what I am getting from the SQL statment I provided:

The column names are:

Employee Name | Follow-Up | Warm Transfer |

Here is the data I get back:

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...

Any ideas???

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

I still need the sample data from the base table to rebuild the correct query.

Salvatore Conti Jr. Ten Centuries Points: 1155 More actions · Answer 5

Sorry Remi...

Here you go:

CURDATE | EMPLOYEE_NME | MANAGER_ID | ACTION_TAKEN

6/22/05 4:43:40 PM | Flintstone,Fred | 30000002 | Follow-up

6/22/05 4:32:40 PM | Flintstone,Fred | 30000002 | Warm Transfer

6/22/05 4:43:40 AM | Flintstone,Fred | 30000002 | Follow-up

6/23/05 1:43:40 PM | Jetson,George| 30000002 | Warm Transfer

6/23/05 6:43:40 PM | Jetson,George| 30000002 | Warm Transfer

6/23/05 4:01:40 PM | Jetson,George| 30000002 | Follow-up

Let me know if this is what you are asking for...I changed the data, but this is how it would look in my table...

Thank you!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Declare @a table (CURDATE smalldatetime, EMPLOYEE_NME varchar(50), MANAGER_ID int, ACTION_TAKEN varchar(20), primary key (CURDATE, MANAGER_ID))

Insert into @a (CURDATE, EMPLOYEE_NME, MANAGER_ID, ACTION_TAKEN)

Select '6/22/2005 4:43:40 PM', 'Flintstone,Fred', 30000002, 'Follow-up'

Insert into @a (CURDATE, EMPLOYEE_NME, MANAGER_ID, ACTION_TAKEN)

Select '6/22/2005 4:32:40 PM', 'Flintstone,Fred', 30000002, 'Warm Transfer'

Insert into @a (CURDATE, EMPLOYEE_NME, MANAGER_ID, ACTION_TAKEN)

Select '6/22/2005 4:43:40 AM', 'Flintstone,Fred', 30000002, 'Follow-up'

Insert into @a (CURDATE, EMPLOYEE_NME, MANAGER_ID, ACTION_TAKEN)

Select '6/23/2005 1:43:40 PM', 'Jetson,George', 30000002, 'Warm Transfer'

Insert into @a (CURDATE, EMPLOYEE_NME, MANAGER_ID, ACTION_TAKEN)

Select '6/23/2005 6:43:40 PM', 'Jetson,George', 30000002, 'Warm Transfer'

Insert into @a (CURDATE, EMPLOYEE_NME, MANAGER_ID, ACTION_TAKEN)

Select '6/23/2005 4:01:40 PM', 'Jetson,George', 30000002, 'Follow-up'

Select

Employee_nme

, sum(case when ACTION_TAKEN = 'Follow-up' THEN 1 ELSE 0 END) as FollowUps

, sum(case when ACTION_TAKEN = 'Warm Transfer' THEN 1 ELSE 0 END) as WarmTransfers

from @a

WHERE MANAGER_ID = 30000002 AND CURDATE BETWEEN '6/22/2005 8:26:48 AM' AND '6/23/2005 5:49:57 PM'

Group byEmployee_nme

Order by Employee_nme

--play with the where condition to get different results

/*

Flintstone,Fred11

Jetson,George11

*/

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 7

The problem with your original SQL is you have ACTION_TAKEN in the Group By

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

That too, but I preffer doing the sum(case) approach... but maybe it's not important.

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 9

Remi,

You already had the solution in your post, I just wanted to make it more clear. And I agree with you, I prefer the Sum(Case) syntax as opposed to (Case When Sum(Field)... I don't know if there are any performance issues with the later, I just think the first is easier to read.

Thanks,

Brian

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Thanx for clarifying. I don't know either if there's a performance difference... but on further analysis I'm not even sure that this query can work the way he's written it.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Yup, it can work, but it takes a derired table to finish the job. Performance is almost the same. But his approach takes lots more typing .