nested CASE? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

nested CASE?

Author

Topic

elisabetj
Starting Member

3 Posts

Posted - 2004-03-02 : 09:25:49

hey everyone,

I need help with a select statement that is causing me lots of trouble. I want to count the number of customer visits each salesperson does per month. at the moment it looks something like this:

SELECT salesperson,
SUM(CASE WHEN MONTH(visitDate)=1 AND YEAR(visitDate)=2004 THEN 1 ELSE 0 END) AS "Jan",
SUM(CASE WHEN MONTH(visitDate)=2 AND YEAR(visitDate)=2004 THEN 1 ELSE 0 END) AS "Feb"

etc etc (march, april...)

FROM Visits
GROUP BY salesperson

this works fine but i would like to narrow my query down a little...if a salesperson visits the same customer twice in a day (or more than 2 times) - they might for example visit 2 people at the same company - i still want those visits to count as one.

Example:

Salesperson Customer Date
S1 C1 2004/03/02
S1 C1 2004/03/02

should count as one visit!!! But if the dates were different they would count as two separate visits. hope you understand what i mean. I use SQLServer 2000 by the way. kind of wanted to include another CASE statement in the one i have...doesn't seem to be working. Any ideas???

would greatly appreciate any help.

thanks in advance
/Lis

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-02 : 10:04:14

You may not need a nested CASE, why not use your existing CASE on a derived table which uses SELECT DISTINCT?

Raymond