select 
	ReportingMonth 
	,Program 
	,SubscriberId
	,MemberId
	,case when SubscriberId = MemberId then 1 else 0 end as Subflag
into temp
from mytable
where year(reportingmonth)= 2010




select 
	Program
	,sum(convert(int,subflag)) as Subs
	,count(memberid) as Members
from temp
where month(reportingmonth) = 12

quote:

---

Originally posted by jcb267

I am trying to create a variable that I call subflag. It says where a subscriberid = memberid then assign a 1 or else assign a 0.

select ReportingMonth
Program
,SubscriberId
,MemberId
,case
when SubscriberId = MemberId then '1' else '0' end as Subflag

into temp

from mytable

where year(reportingmonth)= '2010'

Then I just want to sum the subflag field and count the memberid field by program:

select Program
,sum(convert(int,subflag)) as Subs
,count(memberid) as Members

from temp

where month(reportingmonth) = '12'

I get results, but they do not look correct to me. What am I doing wrong?

---