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 - 2011-05-11 : 11:03:44
|
| Hi -Can anyone please help me??I have a query that runs fine but I need to add one field to it from another table. However the field that I need to add it not unique! Will a subselect get me what I need and keep me from having to group my data? How is one done? This is what I have:declare @yyyy intdeclare @mm intset @yyyy = '2011' set @mm = '04' select pb.SponsorId , convert(varchar(10), pb.PremiumMonth, 101) as BaseDt , select max(anniversarydate) as AnnDt from sponsorpolicy , pb.SponsorName , convert(varchar(50), sp.PolicyNumber) as PolNum , case when sd.sponsorsize = 'DIRECTLAGRE' then 'Large' when sd.sponsorsize = 'Unknown' then 'Unknown' when sd.sponsorsize = 'DIRECTLARGE ' then 'Large' when sd.sponsorsize = 'DIRECT??? ' then 'Unknown' when sd.sponsorsize = 'NONGROUP ' then 'NonGroup' when sd.sponsorsize = 'DIRECTSMALL ' then 'Small' when sd.sponsorsize = 'DIRECT SMALL' then 'Small' when sd.sponsorsize = 'INTERMPARENT ' then 'Intermediary' when sd.sponsorsize = 'DRIECTSMALL' then 'Small' when sd.sponsorsize = 'DIRECT LARGE' then 'Large' when sd.sponsorsize = 'DIRESTSMALL' then 'Small' end as SponSize , pb.CoverageType , case when pb.subscriberid = pb.memberid then '1' else '0' end as Subs , pb.BilledPremiumAmount as Rate from premiumbilling pb left join memberdemographics md on pb.memberid = md.memberid left join sponsorpolicy sp on pb.policyid = sp.policyid left join sponsordemographics sd on pb.sponsorid = sd.sponsoridwhere pb.invoicestatus = 'billed' and pb.billedpremiumamount >= '0' and pb.sponsorid not in ('100002', '100003', '100360') and year(pb.premiummonth) = @yyyy and month(pb.premiummonth) = @mm and pb.subscriberid = pb.memberid |
|
|
|
|
|
|
|
Sub Select?