Wednesday, February 27, 2013 11:45 AM
Appreciate your help and comments to resolve following issue please.
We have a business application which updates business transaction information into a table. ( Table Name: Policy_Trans)
Trans_Id and Policy_Id are primary keys. Trans_Id is (int) type and sequentially increase for each row in the table.
Policy_Id also int type.
Using a stored procedure I am updating a fact table (factTrans) in data warehouse based on information in “Policy_Trans” table.
Stored Procedure does following:
Read each row from “Policy_Trans” and Insert/Update fact Table “factTrans”
When I read a “Policy_Trans” row which has ‘Transaction_Type’ = ‘Surrender’ , for a particular policy, I want to capture its immediate predecessor row’s information as well.
e.g. when I read ‘Trans_Id’ = 355 row, it has ‘Transaction_Type’= ‘Surrender’, then I need to capture details of row which has ‘Trans_id’ = 347. Because I need Premium value of 275 also, for calculation which update my ‘factTrans’ table.
When I am reading row which has ‘Trans_Id’ = 384, I need premium value of 310 in ‘Trans_id’ = 345 row.
Part of stored proc……
MERGE dbo.factTrans AS Target
( SELECT FT.Trans_Id
,CASE WHEN FT.transaction_type = 'Surrender'
THEN DATEADD(yy, 1, P.start_date)
END AS end_date
FROM Policy_Trans as FT
) AS SOURCE
ON ( Target.Trans_id = SOURCE.Id
-- Merge continues
WHEN NOT MATCHED BY TARGET
Thursday, February 28, 2013 9:39 AM
I have allowed myself to get slightly addicted to this challenge! - but I think have got there in the end... phew.
;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
Thursday, February 28, 2013 9:45 AM
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