question

dear Geeks good day to you !!! I am stuck in a very huge problem while creating a calculator of Loan in which Mortgage will be added on daily basis. there are inputs as below CusId,LoanId,LoanDate,InstallmentFrequency,InitialRate,LoanEMI,LoanAmout,TermInMonths on this basis you will get EMI as input and now 1st thing is to make schedule of Emi till outstanding of Loan becomes less than 0.00 and one more thing if there is any ratechange or prepayment you will have to arrange that in order of Transaction Date of, Ratechange and Prepayment information is in another table. now you have two tables in order of Transaction dates now you will have to make calculation till outstanding is 0 or less i have tried it but i am unable to do it.. Please help me as i am new to organization as well as sql also.. declare @CusId int,@LoaId tinyint, @LoanDate datetime, @InstFreq tinyint=1, @InitialRate decimal(9,6)=12.00, @LoanEMI decimal(18,2), @LoanAmout decimal(18,2)=100000.00, @TermInMonths decimal(7,2)=12 --select @LoanEMI=dbo.GetLoanEMI(@InitialRate/12.00,@LoanAmout,@TermInMonths) select @LoanDate=dbo.getdateist(0)-400; /*ROW 1*/ declare @B1 tinyint,@C1 datetime, @D1 smallint,@E1 decimal(18,2),@F1 Decimal(9,6),@G1 Decimal(9,6), @H1 Decimal(18,12),@I1 int,@J1 int,@K1 Decimal(18,6),@L1 decimal(18,2),@M1 decimal(18,2), /*ROW 2*/ @B2 tinyint,@C2 datetime, @D2 smallint,@E2 decimal(18,2),@F2 Decimal(9,6),@G2 Decimal(9,6), @H2 Decimal(18,12),@I2 int,@J2 int,@K2 Decimal(18,6),@L2 decimal(18,2),@M2 decimal(18,2) /*Assigning Values to Row1 Elements*/ set @B1=1; set @C1=@LoanDate; set @D1=0; set @E1=-@LoanAmout; set @F1=@InitialRate/100.00; set @G1=case @InstFreq when 1 then @F1/12 when 2 then @F1/4 when 3 then @F1/2 when 4 then @F1 end; select @H1=0.00031; --select @H1=dbo.GetRateFromFV(100.00,100+@InitialRate,365) ; set @H1=@H1/100.00; set @I1=0; set @J1=0; set @K1=0; set @L1=0.00; set @M1=@LoanAmout; /*Assigning Values to Row2 Elements*/ set @B2=1; /*creating table */ create table #tempp(Auniid int identity(1,1),TranTypId tinyint,TranDate datetime,TranNo smallint, TranAmt decimal(18,2),AnnRate Decimal(9,6),MonRate Decimal(9,6),DayRate Decimal(18,12), MonBetTran int,DaysBetTran int,AccordIntOnDt Decimal(18,6),PrincipalP decimal(18,2),Outstanding decimal(18,2),LoaId tinyint,CusId int) /*1st row*/ insert into #tempp values( @B1/*EMI*/,@C1,@d1,@E1,@F1,@G1,@H1,@I1,@J1,@K1,@L1,@M1,@LoaId,@CusId) select * from #tempp union all select 0,TranTypId=t.TranTypeId,TranDate=t.TranDt,TranNo=0,TranAmt=case t.TranTypeId when 3 then t.TranAmt else 0.00 end, AnnRate=case t.TranTypeId when 2 then t.TranAmt else 0.00 end,MonRate=null,DayRate=null, MonBetTran=null,DaysBetTran=null,AccordIntOnDt=null,PrincipalP=null,Outstanding=null,LoaId,CusId from ( select cusid,LoaId,TranTypeID,Trandt,TranAmt from GPLoanIntrPayRec where CusId=10000180 and LoaId=0 and trantypeid=2 union all select cusid,LoaId,TranTypeID,Trandt,TranAmt from GPLoanIntrPayRec where CusId=10000180 and LoaId=0 and trantypeid=3 ) t order by TranDate declare @i int=2,@cnt int; set @M2=@M1; while(@M2>0.00) begin if(@i=2) begin set @C2=case @InstFreq when 1 then DATEADD(MONTH,1,@C1) when 2 then DATEADD(MONTH,3,@C1) when 3 then DATEADD(MONTH,6,@C1) when 4 then DATEADD(MONTH,12,@C1) end; end else begin set @C2=case @InstFreq when 1 then DATEADD(MONTH,1,@C2) when 2 then DATEADD(MONTH,3,@C2) when 3 then DATEADD(MONTH,6,@C2) when 4 then DATEADD(MONTH,12,@C2) end; end set @D2=case @B2 when 1 then @i-1 else null end; set @E2=case @B2 when 1 then @LoanEMI else 0.00 end; set @F2=case @B2 when 1 then @F1 else @F2 end; set @G2=@F2/12.00; select @H1=dbo.GetRateFromFV(100.00,100+@InitialRate,365); set @H1=@H1/100.00; select @H1=MonRate,@H2=Dayrate,@I2=DATEDIFF(MONTH,TranDate,@C2),@J2=DATEDIFF(DAY,TranDate,@C2),@M1=Outstanding from #tempp where Auniid=@i-1; if(@I2=1) begin set @K2=@I2*@M1*@H1 end else begin set @K2=@J2*@M1*@H1 end set @L2=@E2-@K2; set @M2=@M1-@L2; insert into #tempp values(@B2/*EMI*/,@C2,@D2,@E2,@F2,@G2,@H2,@I2,@J2,@K2,@L2,@M2,@LoaId,@CusId) set @i=@i+1; end select * from #tempp