Help Needed In Table Transformation

• Nilssond

  Hall of Fame

  Points: 3285

  More actions

  March 18, 2004 at 5:37 pm

  #163310

  I have a table structure as follows:

  MemberNbr

  Comment

  EffectiveDate

  A member may have several entries in this table.

   

  I need to transform this data into a new table where each member's succeeding record's effective date becomes the end date for the preceeding record: thus

  MemberNbr  CommentStartDate   CommentEndDate   Comment

  Any suggestions how how to do this?

   

• wz700

  SSCertifiable

  Points: 7133

  More actions

  March 19, 2004 at 2:55 am

  #499454

  self join case:

  select a.memberNbr, a.effectivedate commentstartdate, min(b.effectivedate) commentenddate, a.comment

   from table a

   left outer join table b on a.memberNbr = b.memberNbr and a.effectivedate < b.effectivedate

   group by a.memberNbr, a.effectivedate, a.comment

   order by a.effectivedate

   

  Here assume comments stay the same for a given memberNbr.