SQL Stored Procedure Help

All Replies

• Wednesday, November 21, 2012 10:20 PM

   

   
  

  0

  You are trying to fill in the detail with a monthly amount but you don't have one.

  ---

  If you're happy and you know it vote and mark.

  • Reply
  • Quote

   
• Tuesday, December 04, 2012 12:37 PM

   

   
  

  0

  you could try something like this (please remove any hard coded dates)-

  --create table masterTable
  --(
  --fin_id int
  --,policy_id int
  --, transaction_type varchar(20)
  --,Annual_Premium money
  --, Return_Amount money
  --, Start_Date datetime
  --, End_Date datetime
  --)

  --create table DetailTable
  --(
  --FinID int
  --, Policy_id int
  --, Effective_Date datetime
  --, monthly_net_premium money
  --)

  --insert masterTable (fin_id, policy_id, transaction_type, Annual_Premium, Return_Amount,Start_Date, End_Date) values
  --(215,4402,'New',624,null,'1 Apr 2009','31 Mar 2010')

  --insert masterTable (fin_id, policy_id, transaction_type,Annual_Premium, Return_Amount, Start_Date, End_Date) values
  --(216,4402,'surrender',624,200,'1 Apr 2009','31 Mar 2010')

  declare @Months table
  (
  EffectiveDt datetime
  )
  Declare @dt datetime
  Select @dt = '1 Jan 2009'
  Insert @Months (EffectiveDt) values (@dt)
  Insert @Months (EffectiveDt) values (dateadd(m,1,@dt))
  Insert @Months (EffectiveDt) values (dateadd(m,2,@dt))
  Insert @Months (EffectiveDt) values (dateadd(m,3,@dt))
  Insert @Months (EffectiveDt) values (dateadd(m,4,@dt))
  Insert @Months (EffectiveDt) values (dateadd(m,5,@dt))
  Insert @Months (EffectiveDt) values (dateadd(m,6,@dt))
  Insert @Months (EffectiveDt) values (dateadd(m,7,@dt))
  Insert @Months (EffectiveDt) values (dateadd(m,8,@dt))
  Insert @Months (EffectiveDt) values (dateadd(m,9,@dt))
  Insert @Months (EffectiveDt) values (dateadd(m,10,@dt))
  Insert @Months (EffectiveDt) values (dateadd(m,11,@dt))
  Insert @Months (EffectiveDt) values (dateadd(m,12,@dt));

  WITH c_Daily AS (
  SELECT
   fin_id
   , policy_id
   , transaction_type
   ,Annual_Premium
   , Return_Amount
   , Start_Date
   , End_Date
   ,EffectiveDt
   , CASE
    WHEN transaction_type IN ( 'Surrender' )
     THEN 0    -- I need Help Here #################
    ELSE (Annual_premium) / 12
   END AS monthly_net_premium
  FROM dbo.masterTable x
  cross join @Months y
  )
  Select a.*--,c.*
  ,c.fin_id as detail_fin_id   ,c.policy_id  as detail_policy_id,c.transaction_type  as detail_transaction_type,c.Annual_Premium  as detail_Annual_Premium, c.Return_Amount   as detail_Return_Amount, c.Start_Date   as detail_Start_Date, c.End_Date as detail_End_Date 
   ,a.monthly_net_premium +COALESCE((SELECT SUM(monthly_net_premium)
   FROM c_Daily b
   WHERE b.effectivedt > a.effectivedt ),0)
   AS RunningTotal0
  into #temp1
  from c_Daily a
  join c_Daily c on a.policy_id = c.policy_id and a.EffectiveDt = c.EffectiveDt
  where a.policy_id = 4402 and a.transaction_type = 'New' and c.transaction_type = 'surrender'
  --order by a.effectivedt desc

  select
  a.detail_fin_id, a.detail_policy_id,a.detail_transaction_type, a.detail_annual_premium, a.detail_return_amount, a.detail_start_date, a.detail_end_date, a.monthly_net_premium
  from #temp1 a
  left join #temp1 b on a.EffectiveDt = dateadd(m,-1,b.EffectiveDt )
  where (b.RunningTotal0 <= a.detail_Return_Amount  or b.RunningTotal0 is null)
  and a.RunningTotal0 - a.detail_Return_Amount <=0
  union all

  select a.detail_fin_id, a.detail_policy_id,a.detail_transaction_type, a.detail_annual_premium, a.detail_return_amount, a.detail_start_date, a.detail_end_date, (a.monthly_net_premium - (a.RunningTotal0 - a.detail_Return_Amount)) as monthly_net_premium
  from #temp1 a
  left join #temp1 b on a.EffectiveDt = dateadd(m,-1,b.EffectiveDt )
  where (b.RunningTotal0 <= a.detail_Return_Amount  or b.RunningTotal0 is null)
  and a.RunningTotal0 - a.detail_Return_Amount > 0

  union all

  select a.fin_id, a.policy_id,a.transaction_type, a.annual_premium, a.return_amount, a.start_date, a.end_date, a.monthly_net_premium from #temp1 a 
  where a.policy_id = 4402 and a.transaction_type = 'New'

  --drop table #temp1

  • Marked As Answer by Mira Pimbi Wednesday, December 12, 2012 1:41 AM
  •  
  • Reply
  • Quote

   
• Wednesday, December 12, 2012 1:41 AM

   

   
  

  0

  Hi Varsha

  Fantastic !!, I am working on your code and logic, will post outcome shortly.

  Cheers

  Mira

  • Reply
  • Quote