Is there a smarter way of fixing transposition errors?

• drew.georgopulos

  SSCarpal Tunnel

  Points: 4931

  More actions

  August 4, 2011 at 1:07 pm

  #245738

  Hello list;

  Today's adventure is how to make up for transpostion errors. I am trying to match records of mothers and their newborn babies but the business process makes claims adjusters enter the linking information manually (no drop downs), so it is possible that the linking element is 'broken' because a claims adjuster enters the mother's case number and then the baby's case number with the digits transposed.

  Is there a smart way to do this?

  create table t1 (c1 char(5))

  insert t1 values ('12345') - original case number

  insert t1 values ('13245') - potential match (YNNYY)

  the retrieval method that gets all of them back is too general to be useful, like select * from t1 just returns them all, select * from t1 where c1 like '_____' just returns any number with five digits, and

  select * from t1 where

  c1 like '1____'

  or c1 like '2____'

  or c1 like '3____'

  or c1 like '4____'

  or c1 like '5____'

  only retrives the ones that start with the specified digit. So for the time being, ignoring the other kinds of errors like these,

  insert t1 values ('23451')

  insert t1 values ('34512')

  insert t1 values ('45123')

  insert t1 values ('51234')

  insert t1 values ('13452')

  insert t1 values ('14532')

  insert t1 values ('12453')

  is there a way to record the number in a position, then, if the positions are adjacent, make the assertion that this is really a match?

  I hope this wasnt too muddled.

  Thanks a lot for your time and attention

  drew

• drew.georgopulos

  SSCarpal Tunnel

  Points: 4931

  More actions

  August 4, 2011 at 2:45 pm

  #1363997

  Something along these lines seems promising, but i can't get out of my own way....

  select * from

  (

  select SUBSTRING(c1,1,1)[1], SUBSTRING(c1, 2,1)[2], SUBSTRING(c1, 3,1)[3],

  SUBSTRING(c1, 4,1)[4],SUBSTRING(c1, 5,1)[5]

  from t1

  )x

  unpivot (c1 for x in ([1],[2],[3],[4],[5])) p

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  August 4, 2011 at 11:57 pm

  #1364092

  drew.georgopulos (8/4/2011)

  ---

  Is there a smart way to do this?

  No. There isn't for this particular type of thing. If your "smart" program, whatever it is, makes a bad guess, then you're messing with someone's life.

  This requires human intervention and and manual verification ONLY. 😉

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• drew.georgopulos

  SSCarpal Tunnel

  Points: 4931

  More actions

  August 5, 2011 at 6:57 am

  #1364315

  thanks very much Mr. Moden, I really appreciate your help

  drew