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 |
|
jcb267
Constraint Violating Yak Guru
291 Posts |
 Posted - 2010-11-08 : 10:04:17
|
| I am trying to add a column to my query that counts subscribers when subscribername = membername. I can't get this to work though. This is what I have so far:select dbo.PremiumBilling.PremiumMonth, dbo.PremiumBilling.InvoiceDate, dbo.PremiumBilling.InvoiceStatus, dbo.PremiumBilling.BillToSponsorId, dbo.PremiumBilling.SponsorId, dbo.PremiumBilling.SponsorName, dbo.SponsorDemographics.SponsorSize, dbo.PremiumBilling.PolicyNumber, dbo.PremiumBilling.SubscriberId, dbo.PremiumBilling.SubscriberName, ( count(dbo.PremiumBilling.SubscriberName) where (dbo.PremiumBilling.SubscriberName = dbo.PremiumBilling.MemberName) as SubCT ), dbo.PremiumBilling.MemberName, dbo.PremiumBilling.LineOfBusiness, dbo.PremiumBilling.Program, dbo.PremiumBilling.BenefitPlan, dbo.PremiumBilling.RateCode, dbo.PremiumBilling.PremiumDays, dbo.PremiumBilling.PremiumMonthDays, dbo.PremiumBilling.MemberMonths, dbo.PremiumBilling.PremiumAmount, dbo.PremiumBilling.BilledPremiumAmountfrom dbo.PremiumBilling INNER JOIN dbo.SponsorDemographics ON dbo.PremiumBilling.SponsorId = dbo.SponsorDemographics.SponsorId |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2010-11-08 : 10:49:17
|
| [code]dbo.PremiumBilling.SubscriberName,SUM(CASE WHEN dbo.PremiumBilling.SubscriberName = dbo.PremiumBilling.MemberName THEN 1 ELSE 0 END) AS SubCT,dbo.PremiumBilling.MemberName, [/code] |
 |
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2010-11-08 : 14:44:14
|
Thank you.quote:
Originally posted by Ifor
dbo.PremiumBilling.SubscriberName,SUM(CASE WHEN dbo.PremiumBilling.SubscriberName = dbo.PremiumBilling.MemberName THEN 1 ELSE 0 END) AS SubCT,dbo.PremiumBilling.MemberName,
|
|
|
|
|
|
|
|
|
Record Count