deletting the two adjacent same records.

• punia

  SSC-Addicted

  Points: 427

  More actions

  August 24, 2011 at 2:27 pm

  #256077

  Hi All,

  I have the table with following data:

  Claim_ID,ClaimStatusCode,Statusdate,cycle

  40356442,Closed,2/1/2008,1

  40356442,Open,2/6/2008,1

  40363300,Closed,6/4/2008,1

  40363300,Open,6/17/2008,1

  40363300,Closed,3/23/2009,2

  40365497,Closed,7/30/2010,1

  40365497,Open,8/3/2010,1

  40365497,Closed,6/27/2011,2

  40365497,Open,6/30/2011,2

  40374968,Closed,12/19/2007,1

  40374968,Open,12/24/2007,1

  40374968,Open,9/8/2008,1 --to be deleted

  40389312,Closed,1/4/2008,1

  40389312,Open,1/18/2008,1

  40389312,Open,10/21/2008,1 --to be deleted

  40390976,Closed,2/12/2009,1

  40390976,Open,2/17/2009,1

  40390976,Closed,4/6/2010,2

  40390976,Open,4/8/2010,2

  40390976,Closed,3/18/2011,3

  Cycle represent the number from closed status till open, if there is other next closed , then it will be next cycle.

  Result: For each claim_Id if the last two statusdates are Open, then it should delete the max Open statusdate, that is

  Claim_ID,ClaimStatusCode,Statusdate,cycle

  40356442,Closed,2/1/2008,1

  40356442,Open,2/6/2008,1

  40363300,Closed,6/4/2008,1

  40363300,Open,6/17/2008,1

  40363300,Closed,3/23/2009,2

  40365497,Closed,7/30/2010,1

  40365497,Open,8/3/2010,1

  40365497,Closed,6/27/2011,2

  40365497,Open,6/30/2011,2

  40374968,Closed,12/19/2007,1

  40374968,Open,12/24/2007,1

  40389312,Closed,1/4/2008,1

  40389312,Open,1/18/2008,1

  40390976,Closed,2/12/2009,1

  40390976,Open,2/17/2009,1

  40390976,Closed,4/6/2010,2

  40390976,Open,4/8/2010,2

  40390976,Closed,3/18/2011,3

  records tobe deleted from table are:

  40374968,Open,9/8/2008,1 --to be deleted

  40389312,Open,10/21/2008,1 --to be deleted

  i tried doing this by max date join with table and row_number as well, but no luck.

  Any help is aprreciated.

  Thanks,

  Punia

• LutzM

  SSC Guru

  Points: 107049

  More actions

  August 24, 2011 at 4:32 pm

  #1372882

  I would start with

  ROW_NUMBER() OVER (PARTITION BY Claim_ID ORDER BY Statusdate ) +

  ROW_Number() OVER (PARTITION BY Claim_ID,ClaimStatusCode ORDER BY Statusdate desc)

  to get a group number per consecutive range.

  Depending on the requirements (only find the latest duplicate or all dupes after the the first one in the last group e.g. in order to delete the last two if there are three) I might add another row_number to get groups numbered internally.

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• Jeff Moden

  SSC Guru

  Points: 1004641

  More actions

  September 7, 2011 at 6:42 pm

  #1378317

  I believe that I'd like to know how a claim was closed before it was opened. From the original data...

  40389312 ,Closed ,1/4/2008 ,1

  40389312 ,Open ,1/18/2008 ,1

  40389312 ,Open ,10/21/2008 ,1 --to be deleted

  Is that because the "Closed" may have had an earlier "Open" outside of "the cycle"?

  --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)