Cross Referencing Records

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  November 15, 2006 at 12:08 pm

  #117620

  I have a member table with the following columns:

  MemberNbr varchar(11) , AlternateMemberNbr varchar(11) , PlanType varchar(3)

  There are instances when 2 records will apply to the same individual, in which case the cross-reference will be the AlternateMemberNbr column. Each AlternateMemberNbr should contain the valid and existing MemberNbr for the referencing record;

  Example:

  MemberNbr          AlternateMemberNbr        PlanType

  12345678901       23456789012                  ABC

  23456789012       12345678901                  XYX

  I need to find records where 1 of the records has a valid AlternateMemberNbr

  but the other record does not, or more importantly, where the other record is missing the AlternateMemberNbr.

   

• Vasc

  SSCarpal Tunnel

  Points: 4643

  More actions

  November 15, 2006 at 12:33 pm

  #672103

  DECLARE

  @T TABLE(MemberNbr varchar(15),AlternateMemberNbr varchar(15),PlanType varchar(15))

  INSERT

  INTO @T

  SELECT

  '12345678901', '23456789012', 'ABC' UNION ALL

  SELECT

  '23456789012', '12345678901', 'XYX' UNION ALL

  SELECT

  '23456789013', '12345678905', 'XY1'

   

  SELECT

  A.* , B.*

  FROM

  @T A LEFT OUTER JOIN @T B ON A.AlternateMemberNbr=B.MemberNbr

  --WHERE B.MemberNbr IS NULL --will return the records where the AlternateMember doesn t have a MemberNbr

  ---

  Kindest Regards,

  Vasc