How to capture immediate predecessor of a set of rows in a Table?

All Replies

• Thursday, February 28, 2013 9:39 AM

   

   
  

  0

  Good morning!

  I have allowed myself to get slightly addicted to this challenge! - but I think have got there in the end... phew.

  Try this:

  ;with cte
  as
  (
  select
   Trans_Id
  ,policy_id
  ,Transaction_Type
  ,row_number() over (partition by policy_id order by Trans_Id desc) as rn
  ,Premium
  from #Policy_Trans
  )
  select
   FT.Trans_Id
  ,FT.policy_id
  ,FT.Transaction_Type
  ,FT.premium
  ,FT.start_date
  ,FT.end_date
  ,CASE WHEN FT.transaction_type = 'Surrender'
  	THEN DATEADD(yy, 1, FT.start_date)
      ELSE FT.end_date
  END AS Pend_date
  ,coalesce(FT2.Trans_id,FT.Trans_Id) as Chosen_Trans_Id
  ,coalesce(FT2.Premium,FT.Premium) as Chosen_Premium
  FROM      #Policy_Trans as FT
  left join cte as FT2
  on FT.Policy_Id=FT2.Policy_Id
  	and (FT.Trans_Id <> FT2.Trans_Id)
  	and FT2.rn < 3
  	and FT.Transaction_Type='Surrender'
  	order by 1 asc

  If you are using SQL 2012 then you may be able to achieve this using the LAG() function but otherwise as described above you will need a self join. I am having to use a cte to give us RowNumber with the Row_Number windowing function, so we can join and just receive one row (like a TOP 1 really) in the main query.

  Result sets look like this:

  

  ---

  MCSA: (SQL 2012, SQL 2008, Windows Server 2008) MCITP: (SQL BI & SQL 2008) MCT: 2000 - 2013 SQL Training

  
  

  • Edited by Lawrence A. Freeman Thursday, February 28, 2013 9:42 AM
  • Proposed As Answer by Lawrence A. Freeman Thursday, February 28, 2013 9:42 AM
  • Marked As Answer by Mira Pimbi Thursday, February 28, 2013 10:51 PM
  •  
  • Reply
  • Quote

   
• Thursday, February 28, 2013 9:45 AM

   

   
  

  0

  My first thoughts on this are to add a column to your Policy_Trans table to hold a sequence number associated with the Policy_Id.  This would allow you to do a simple self-join and pick up the previous record's data by finding the record with a matching Policy_Id but with previous sequence number.
  

  What version of SQL are you on?  If it's 2012 then you can make use of the LAG function for this.  Otherwise, without making the change above you'll need to do a self-join looking for the highest Trans_Id that is lower than your 'Surrender' record, a bit like this:

  FROM Policy_Trans a
  LEFT OUTER JOIN
     (SELECT a.Policy_Id, MAX(b.Trans_Id) Prev_Trans_Id
      FROM Policy_Trans a
      INNER JOIN Policy_Trans b
      ON  a.Policy_Id = b.Policy_Id
      AND a.Trans_Id > b.Trans_Id
      WHERE a.Transaction_Type = 'Surrender'
      GROUP BY a.Policy_Id) b
  ON  a.Policy_Id = b.Policy_Id
  LEFT OUTER JOIN Policy_Trans c
  ON  b.Policy_Id = c.Policy_Id
  AND b.Prev_Trans_Id = c.Trans_Id

  As you can see, it's not very pretty, and could cause some performance issues.  If you're only after the Premium from the previous record, there is a little trick you can use to cut out one of the joins above, which is to pull back the Premium from within the subquery:

  FROM Policy_Trans a
  LEFT OUTER JOIN
     (SELECT a.Policy_Id
            ,CONVERT(int,RIGHT(MAX(RIGHT('0000000000'+CONVERT(varchar(b.Trans_Id),10)+RIGHT('0000000000'+CONVERT(varchar(b.Premium),10)),10)) Prev_Premium
      FROM Policy_Trans a
      INNER JOIN Policy_Trans b
      ON  a.Policy_Id = b.Policy_Id
      AND a.Trans_Id > b.Trans_Id
      WHERE a.Transaction_Type = 'Surrender'
      GROUP BY a.Policy_Id) b
  ON  a.Policy_Id = b.Policy_Id
  

  NOTE - I've assumed the Premium is an int for the above example, so the logic may need tweaking for other data types. 

  What I've done is convert the numbers to varchars and padded them with zeros.  This maintains the sort order.  I've concatenated the Premium to the end in the same way.  After taking the max value (which equates to the max Trans_Id, i.e. the previous record) I then remove the Premium from the end of the string and convert back to an int.

  • Marked As Answer by Mira Pimbi Thursday, February 28, 2013 10:51 PM
  •  
  • Reply
  • Quote