I am trying to get one person and show my current insurance with my existing insurance. I think someone can say that I am trying to clarify my view of customers or people. I am running on an issue where many records are coming back because of the existing multiple records in my left-wing sub-clonies. I was hoping that I can solve it by adding "Top 1" to subquery, but in reality Nothing gives returns ...
Any thoughts?
SELECT p.person_id AS 'first' as 'mIRID', p.firstname, 'last' as p.lastname, pg.name as 'group' E.name 'AOR', 'contact date' as p.leaddate, [dbo]. [GetPICampaignDisp] (p.person_id, '2009') AS 'PI - 2009'., [Dbo] [GetPICampaignDisp] (p.person_id, '2008's) AS' PI - 2008 '., [Dbo] [GetPICampaignDisp] ( P .person_id, 2007 ')' PI - as' 2007, a_disp.name as' total DISP ', a_ins.name as "Co-ins', a_prodtype.name as' total iNS type, a_t.date 'as total iNS APP date', a_t.effdate as' total iNS EFF date ', b_disp.name as' Last DISP', b_ins.name as "Back-ins', b_prodtype. As 'previous INS type' as name, 'previous INS' as b_t.date PP date ', B_T.Fedet AS' Pre VINS EFF DATE ', B_T.Ramdate AS' from the pre-time 'date of expiry of date [Person] Amal oyee_id = p.agentofrecord_id Join Insider [dbo]. [Person_physician] pp.person_id at PP = pp.person_id Join [dbo]. [Physician] ph.physician_id pH = pp.physician_id join interi [dbo]. [Clinic] c at c.clinic_id = ph.clinic_id join [dbo]. [D_Physgroup] Select pg.d_physgroup_id = c.physgroup_id Left outer JOIN (Trl to graduate. * FROM [transaction] Trl Left Outer Join [D_vendor] Insl on ins1. D_vendor_id = tr1.d_vendor_id LEFT OUTER JOIN [d_product_type ] Prodtype1 ON prodtype1.d_product_type_id = tr1.d_product_type_id upper outer zone [d_commission_type] ctype1 ON ctype1.d_commission_type_id = tr1.d_commission_type_id WHERE prodtype1.name & lt; & gt; 'Medicare Part D' and tr1.termdate IS NULL) AST_T.Person_ID = as Peepisiardiadiadi Afaianduaiandeedia_vindor_aidi = Ak_teedi_vindor_aidiaanaiansdi_vindor_aidi = on a_t.d_vendor_id Leave it [D_product_type] a_prodtype on a_prodtype.d_product_type_id = a_t .d_product_type_id LEFT OUTER JOIN [d_commission_type] a_ctype on a_ctype.d_commission_type_id = a_t.d_commission_type_id LEFT OUTER JOIN [d_disposition] a_disp on a_disp.d_disposition_id = a_t.d_disposition_id left OUTER Join (SELECT tr2. * FROM [transaction] TR2 LEFT OUTER JOIN [d_vendor] ins2.d_vendor_id = tr2.d_vendor_id at ins2 LEFT OUTER JOIN [d_product_type] prodtype2.d_product_type_id = tr2.d_product_type_id on left external contact [d_commission_type] ctype2 ON ctype2.d_commission_type_id = tr2.d_commission_type_id WHERE prodtype2.name & lt; & gt; 'Medicare Part D' and not Tr2.Tormat) ASB_TO on BITT.Person_ID = PPCRDADID left otters Oin [Di_vender] Bi_aian on Bi_ins Di_vender_aidi = Bi_ Join Tiadi_vindor_aidi left Otter [Di_ Prodkt_prop] Bi_prodtaip on Bi_proditiapkdiaiprodkt_taip_aidi = B_t.d_product_type_id LEFT OUTER JOIN [d_commission_type] b_ctype on b_ctype.d_commission_type_id = b_t.d_commission_type_id LEFT OUTER JOIN [d_disposition] Bi_ Diaispi on Bi_ Diaipeepeedi_ Diaiseepiasn_aidi = Bi_ Teedi_ Diaiseepiasn_aid WHERE pg.d_physgroup_id = @fysGroupID
I Usually used for this type of
Select the person from which
join the left subnet as s1.personid = person.personid on S1...
where not present
(select 1 from subtotal) WHERE Personid = person.personid
and order delete> s1.orderbydate)
1 segment avoids and perhaps makes it a little clearer.
BTW, the way in which I normally put this question together, I just leave arguments, rationally argue the tables and columns; And you can get some performance (but least magnificence) by listing the columns for tr1 and tr2 instead of "tr1. *" And "tr2. *"
Comments
Post a Comment