Is it possible to add the values of case statements? 1

[jwpward]

Hi

I'm a bit of a sql novice and am writing the following stored proc to select the following fields.

select i3_rowid, companyname, phonenumber, companytitle, companyforename, companysurname, datacode,
case when(surname1 <> '' and surname1 is not null) then 1 else 0 end as subs
from calllist
where i3_rowid in
(select i3_rowid from callhistory where finishcode = 'Quote Sent' and
calldate between '19 july 2005 00:00' and '19 july 2005 23:59')


There are 11 more surname fields, which I need to include in the 'subs' field, i.e. I need some code which says 'if surname1 <> '' 1 else 0 + if surname2 <> '' then 1 else 0 etc', to give me the total number of subscribers to this product in one field. Is this possible with case statements, or am i going about it the wrong way?

Thanks a lot in advance.

 

[Catadmin]

That is a very interesting challenge. I've never tried that before.

Have you tried declaring a variable? Something like:

Declare @SurNameCount int

Set @SurNameCount = 0

select i3_rowid, companyname, phonenumber, companytitle, companyforename, companysurname, datacode,  
case 
  when(surname1 <> '' and surname1 is not null) 
  then Set @SurNameCount = @SurNameCount + 1 
  else Set @SurNameCount = @SurNameCount
  end as subs
from calllist 
where i3_rowid in 
(select i3_rowid from callhistory where finishcode = 'Quote Sent' and
calldate between '19 july 2005 00:00' and '19 july 2005 23:59'

Not sure a Set statement will work with a CASE statement, but it's worth a shot. If it doesn't, consider trying a Count() function around your CASE statement.

Let us know if those suggestions don't help.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[jwpward]

Hi Catadmin

Thanks for your reply. Logic-wise it makes perfect sense to create a variable to keep a running total, but as you sugested it doesn't seem to like the syntax.

I also tried using the count function, but it wouldn't allow me to count more than one result of a case statement.

I'll keep trying. It must be possible!

Thanks again

 

[Catadmin]

My other suggestion is to run your query into a temptable that has an extra column for your count, then add the Count statements or the Set @SurNameCount statements seperately, after the select, as an insert to the appropriate records in the Temp table.

Then just do a Select * from #MyTempTable.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Pattycake245]

select i3_rowid, companyname, phonenumber, companytitle, companyforename, companysurname, datacode,  
sum(case when surname1 <> '' and surname1 is not null then 1 else 0
	 when surname2 <> '' and surname2 is not null then 1 else 0
	 when surname3 <> '' and surname3 is not null then 1 else 0 
	 when surname4 <> '' and surname4 is not null then 1 else 0
	 when surname5 <> '' and surname5 is not null then 1 else 0
	 when surname6 <> '' and surname6 is not null then 1 else 0
	 when surname7 <> '' and surname7 is not null then 1 else 0
	 when surname8 <> '' and surname8 is not null then 1 else 0
	 when surname9 <> '' and surname9 is not null then 1 else 0
	 when surname10 <> '' and surname10 is not null then 1 else 0
	 when surname11 <> '' and surname11 is not null then 1 else 0 end) subs
from calllist 
where i3_rowid in 
(select i3_rowid from callhistory where finishcode = 'Quote Sent' and
calldate between '19 july 2005 00:00' and '19 july 2005 23:59'

Tim

 

[Pattycake245]

Never mind, that won't work. Try this:

select i3_rowid, companyname, phonenumber, companytitle, companyforename, companysurname, datacode,  
(case when surname1 <> '' and surname1 is not null then 1 else 0 end + 
case when surname2 <> '' and surname2 is not null then 1 else 0 end + 
case when surname3 <> '' and surname3 is not null then 1 else 0 end + 
case when surname4 <> '' and surname4 is not null then 1 else 0 end + 
case when surname5 <> '' and surname5 is not null then 1 else 0 end + 
case when surname6 <> '' and surname6 is not null then 1 else 0 end + 
case when surname7 <> '' and surname7 is not null then 1 else 0 end + 
case when surname8 <> '' and surname8 is not null then 1 else 0 end + 
case when surname9 <> '' and surname9 is not null then 1 else 0 end + 
case when surname10 <> '' and surname10 is not null then 1 else 0 end + 
case when surname11 <> '' and surname11 is not null then 1 else 0 end) subs
from calllist 
where i3_rowid in 
(select i3_rowid from callhistory where finishcode = 'Quote Sent' and
calldate between '19 july 2005 00:00' and '19 july 2005 23:59')

Tim

 

[jwpward]

Hi

I've been out of the office for a couple of hours. Thanks so much for both of your contributions.

Tim, that last bit of code works a treat.

Thanks again