SQL Query to Calculate Surrender Amounts

I recently received a request to handle closing out repayment data in a SQL query as per below. (Answer at the end.) Hi All, I am working on a stored procedure which use to update a Table, I have come across a business request, which I need some assistance of developing suitable logic: Appreciate if someone can provide some advice on how to handle below situation in SQL please. My main question on this regard is how to distribute 'Surrender' ( Return Amount ) as per below Logic???? I have two tables,“Master Table” and “Detail_Table”, a stored Proc been used to populate “Detail_Table” based on records in“Master Table” Pseudo Code explanation: Read a record by record from “Master Table” and update "Detail Table" If ‘Transaction Type’ = ‘New’ then Put “Monthly_Net_premium” as Annual_premium/12 into “Detail_Table” NEXT #### My concern is how to handle 'Surrender' ## ### If ‘Transaction Type = “Surrender” then Update “Detail_Table” as below logic ( Which I have no idea how to do ))) Reverse Monthly values received from the policy, reversing from last premium received until it reaches Return Amount. (e.g. 52 + 52 + 52 + 44 as Return amount = 200) Master Table record containing Fin_id =216, is the record for 'Surrender', which has Annual Premium value(624) as well , and we know it distributed (i.e. Annual_premium/12 ) ** What is the methodology to use to apportion Return amount , compensating what had received until it reached return amount?? e.g. of processing first two records in "Master Table" shown in Tables Master Table Fin_id Policy_id Transaction Type Annual Premium Return Amount Start Date End Date 215 4402 New 624 - 1/Apr/2009 31/Mar/2010 216 4402 Surrender 624 (200) 1/Apr/2009 31/Mar/2010 Detail_Table Fin_Id Policy_ID Effective_Date Monthly_Net_Premium 215 4402 April-2009 52 215 4402 May-2009 52 215 4402 June-2009 52 215 4402 July-2009 52 215 4402 August-2009 52 215 4402 September-2009 52 215 4402 October-2009 52 215 4402 November-2009 52 215 4402 December-2009 52 215 4402 January-2010 52 215 4402 February-2010 52 215 4402 March-2010 52 216 4402 December -2009 (44) 216 4402 January-2010 (52) 216 4402 February-2010 (52) 216 4402 March-2010 (52) Part of stored procedure: USE [Mira_Test] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[UpdateDetail] AS BEGIN SET NOCOUNT ON; WITH c_Daily AS ( SELECT fin_id , policy_id , transaction_type , Return_Amount , Start_Date , End_Date , direct_debit_details_id , Surrender_Date , CASE WHEN transaction_type IN ( 'Surrender' ) THEN 0 -- I need Help Here ################# ELSE (Annual_premium) / 12 END AS monthly_net_premium FROM dbo.masterTable ) -- Now generate a row for each transaction day INSERT dbo.DetailTable ( FinID , Policy_id , Effective_Date , monthly_net_premium --- some joins etc... END; RETURN 0; Heres the answer Mira Here is some SQL that may help. I have put in an update statement for a single pass and also a slect statement, so you can test it. Basically, it works out the number of whole months that you need to take the premium out of the return amount then it works out the balance. It does it in reverse order based on the business rule. Update Detail_Table Set Monthly_Net_Premium = CASE WHEN transaction_type IN ( 'Surrender' ) THEN (case When DATEDIFF(month, Effective_Date, End_date) = (floor(-1*Return_amount / (Annual_Premium/12) )) then (-1*Return_amount - ((floor(-1*Return_amount / (Annual_premium/12))) * Annual_premium/12)) else ((Annual_premium) / 12) end) ELSE (Annual_premium) / 12 end from Detail_Table inner join Master_Table on Detail_Table.Policy_id = Master_Table.Policy_id and Detail_Table.Fin_id = Master_Table.Fin_id Select Detail_Table.Policy_id, Detail_Table.fin_id, Monthly_Net_Premium, transaction_type, DATEDIFF(month, Effective_Date, End_date), (floor(-1*Return_amount / (Annual_Premium/12))), DATEADD(month, -1*(floor(-1*Return_amount / (Annual_Premium/12) ))-1, End_date), Effective_Date, (-1*Return_amount - ((floor(-1*Return_amount / (Annual_premium/12))) * Annual_premium/12)), ((Annual_premium) / 12) from Detail_Table inner join Master_Table on Detail_Table.Policy_id = Master_Table.Policy_id and Detail_Table.Fin_id = Master_Table.Fin_id Regards Cameron