asked
Add New Row After Every LoanNo-Change in SQL Query for Loan/Interest Upto Now() Calculation
Dear Sirs/Mams, Kindly request to run the query below and suggest the solution required: ----------------------------------------------------------------------- DECLARE @Loan TABLE( CustId INT, LoanNo INT, FromDate Datetime, ToDate Datetime, Months Float, IntRate Float, Opening Float, Debit Float, Credit Float, Closing Float ) INSERT INTO @Loan SELECT 1,1,'01/01/2014','10/03/2014',null,36,6000,Null,200,Null INSERT INTO @Loan SELECT 1,2,'02/01/2014','08/03/2014',null,36,2500,Null,0,Null INSERT INTO @Loan SELECT 2,3,'05/01/2014','07/03/2014',null,36,8000,Null,4000,Null INSERT INTO @Loan SELECT 2,4,'08/01/2014','02/03/2015',null,36,12000,Null,3500,Null INSERT INTO @Loan SELECT 3,5,'10/01/2014','05/03/2015',null,36,60000,Null,48000,Null Select CustId,LoanNo,FromDate,ToDate, MONTH(todate-FromDate) as Months, IntRate, Opening,Opening*MONTH(todate-FromDate)/12*IntRate/100 as Debit, Credit, Opening+(Opening*MONTH(todate-FromDate)/12*IntRate/100)-Credit as Closing from @Loan Order By CustId,FromDate ----------------------------------------------------------------------- this is the output of above query. CustId LoanNo FromDate ToDate Months IntRate Opening Debit Credit Closing 1 1 2014-01-01 00:00:00.000 2014-10-03 00:00:00.000 10 36 6000 1800 200 7600 1 2 2014-02-01 00:00:00.000 2014-08-03 00:00:00.000 7 36 2500 525 0 3025 2 3 2014-05-01 00:00:00.000 2014-07-03 00:00:00.000 3 36 8000 720 4000 4720 2 4 2014-08-01 00:00:00.000 2015-02-03 00:00:00.000 7 36 12000 2520 3500 11020 3 5 2014-10-01 00:00:00.000 2015-05-03 00:00:00.000 8 36 60000 14400 48000 26400 ----------------------------------------------------------------------------------- But I want to attain followings, if you all kindly help me out: 1) Add a row below every LoanNo-Change. 2) Values of the rows as below: CustId= Previous Row's CustId LoanNo= Previous Row's LoanNo FromDate= Previous Row's Todate ToDate=Now() Months=Month(Now()-Previous Row's Todate IntRate=Previous Row's IntRate Opening=Previous Row's Closing Debit=Previous Row's Closing*IntRate/100*Months/12 Credit=Sum(Credit) Closing=Opening+Debit-Closing Thus, kindly suggest me the query modification to get the resultant... Regards- Sanjeeb
Some questions: 1) How would you know which row is a row from the table and which row is "extra" information? 2) When you ask for SUM(credit), how is that to be grouped? 3) In your sample data, each row is a new LoanNo. In a real scenario, would there be multiple rows with the same LoanNo?
yes sir, answers: 1) It means we have to add rows below every loanno ends: i.e 1 row below loanno.1, 1 row below loanno.2 and so on. 2)to be grouped by loanno 3)yes, there is many chances like one can pay installments many times against the same loanno. until full clearance. In that case amounts will be credited and rows will increase accordingly. For Example: Complete Calculation Against LoanNo.1: CustId LoanNo FromDate ToDate Months IntRate Opening Debit Credit Closing 1 1 2014-01-01 00:00:00.000 2014-10-03 00:00:00.000 10 36 6000 1800 200 7600 1 1 2014-10-03 00:00:00.000 2014-12-03 00:00:00.000 2 36 7600 456 3000 5056 1 Tot 2014-12-03 00:00:00.000 GetDate()=2015-06-10 6 36 6000 2256 3200 5056 Accordingly, the calculations should be for other loanno. kindly help me to generate the result. Regards- sanjeeb
Where are you trying to add the new row? In the data returned, or in a report? If you're trying to add it to a report, look at grouping and subtotals after each group.
Dear Alvind Sir, The Grouping and Subtotals couldn't fill the requirement. Because you already knew that I need custom calculated data on **previous row's and Getdate()** as example for loanno.1 is provided. Thus, kindly help me to add rows in report i.e. query. Regards- Sanjeeb
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
DECLARE @Loan TABLE( CustId INT, LoanNo INT, FromDate Datetime, ToDate Datetime, Months Float, IntRate Float, Opening Float, Debit Float, Credit Float, Closing Float ) INSERT INTO @Loan SELECT 1,1,'01/01/2014','10/03/2014',null,36,6000,Null,200,Null INSERT INTO @Loan SELECT 1,2,'02/01/2014','08/03/2014',null,36,2500,Null,0,Null INSERT INTO @Loan SELECT 2,3,'05/01/2014','07/03/2014',null,36,8000,Null,4000,Null INSERT INTO @Loan SELECT 2,4,'08/01/2014','02/03/2015',null,36,12000,Null,3500,Null INSERT INTO @Loan SELECT 3,5,'10/01/2014','05/03/2015',null,36,60000,Null,48000,Null ;WITH cteLoan AS ( SELECT CustId,LoanNo,FromDate,ToDate,Months,IntRate,Opening,Debit,Credit,Closing ,ROW_NUMBER()OVER(PARTITION BY CustID ORDER BY LoanNo ASC, FromDate ASC) aS RN ,ROW_NUMBER()OVER(ORDER BY LoanNo ASC, FromDate ASC) aS RN_ALL FROM @Loan ) ,cte2 AS( SELECT L1.CustId ,ISNULL(L1.LoanNo,L2.LoanNo) AS LoanNo ,L1.ToDate AS FromDate -- previous Date ,GETDATE() AS ToDate -- current date ,MONTH(GETDATE()-L1.ToDate) as Months --??? ,L1.IntRate AS IntRate --Previous Row's IntRate ,L1.Opening AS Opening --Previous Row's Closing ,L1.Opening*MONTH(GETDATE()-L1.FromDate)/12*L1.IntRate/100 AS Debit --Previous Row's Closing*IntRate/100*Months/12 ,L1.Credit ,L1.Opening + L1.Opening*MONTH(GETDATE()-L1.FromDate)/12*L1.IntRate/100 - L1.Credit AS Closing ,CASE WHEN L1.LoanNo <> ISNULL(L2.LoanNo,L1.LoanNo) --AND L1.RN_ALL <> L2.RN_ALL THEN 1 ELSE 0 END AS Select_IT FROM cteLoan AS L1 LEFT JOIN cteLoan AS L2 ON L1.RN + 1= L2.RN AND L1.CustID = L2.CustID ) SELECT CustId ,LoanNo, FromDate ,ToDate,MONTH(todate-FromDate) AS Months ,IntRate , Opening , Opening*MONTH(todate-FromDate)/12*IntRate/100 AS Debit , Credit , Opening+(Opening*MONTH(todate-FromDate)/12*IntRate/100)-Credit AS Closing FROM cteLoan UNION ALL SELECT CustId ,LoanNo, FromDate ,ToDate,Months ,IntRate ,Opening ,Debit , Credit, Closing FROM cte2 WHERE Select_IT = 1 ORDER BY CustID, LoanNo, FromDate CustId LoanNo FromDate ToDate Months IntRate Opening Debit Credit Closing 1 1 2014-01-01 00:00:00.000 2014-10-03 00:00:00.000 10 36 6000 1800 200 7600 1 1 2014-10-03 00:00:00.000 2015-06-14 19:55:49.367 9 36 6000 1080 200 6880 1 2 2014-02-01 00:00:00.000 2014-08-03 00:00:00.000 7 36 2500 525 0 3025 2 3 2014-05-01 00:00:00.000 2014-07-03 00:00:00.000 3 36 8000 720 4000 4720 2 3 2014-07-03 00:00:00.000 2015-06-14 19:55:49.367 12 36 8000 480 4000 4480 2 4 2014-08-01 00:00:00.000 2015-02-03 00:00:00.000 7 36 12000 2520 3500 11020 3 5 2014-10-01 00:00:00.000 2015-05-03 00:00:00.000 8 36 60000 14400 48000 26400
Respected Sir, thankx for being here also to help me out. please use the below data for better dictation: INSERT INTO @Loan SELECT 1,1,'01/01/2014','10/03/2014',null,36,6000,Null,200,Null INSERT INTO @Loan SELECT 1,1,'10/03/2014','12/03/2014',null,36,0,Null,3000,Null INSERT INTO @Loan SELECT 1,2,'02/01/2014','08/03/2014',null,36,2500,Null,0,Null INSERT INTO @Loan SELECT 2,3,'05/01/2014','07/03/2014',null,36,8000,Null,4000,Null INSERT INTO @Loan SELECT 2,4,'08/01/2014','02/03/2015',null,36,12000,Null,3500,Null INSERT INTO @Loan SELECT 3,5,'10/01/2014','05/03/2015',null,36,60000,Null,48000,Null sir below is the output for Against Complete Calculation Against custid.1 & LoanNo.1: The Grouping and Subtotals couldn't fill the requirement. I need custom calculated data as follows: 1)FromDate=Previous row's ToDate 2)ToDate= Getdate()-FromDate (resulted from Previous row's ToDate) For Example: : CustId LoanNo FromDate ToDate Months IntRate Opening Debit Credit Closing 1 1 2014-01-01 00:00:00.000 2014-10-03 00:00:00.000 10 36 6000 1800 200 7600 1 1 2014-10-03 00:00:00.000 2014-12-03 00:00:00.000 2 36 7600 456 3000 5056 --Sir, I have added this row for extra to show that there may be multiple rows against a single loanno. you are also requested to add this row as initial data available for better understanding. 1 Tot 2014-12-03 00:00:00.000 GetDate()=2015-06-10 6 36 6000 2256 3200 5056 --this row has to be added dynamically against every loanno as well as custid and also for grand total. Regards- Sanjeeb
18 People are following this question.
