Home > OS >  SQL Divide previous row balance by current row balance and insert that value into current rows colum
SQL Divide previous row balance by current row balance and insert that value into current rows colum

Time:05-11

I have a table where like this.

Year ProcessDate Month Balance RowNum Calculation
2022 20220430 4 22855547 1
2022 20220330 3 22644455 2
2022 20220230 2 22588666 3
2022 20220130 1 33545444 4
2022 20221230 12 22466666 5

I need to take the previous row of each column and divide that amount by the current row.

Ex: Row 1 calculation should = Row 2 Balance / Row 1 Balance (22644455/22855547 = .99% ) Row 2 calculation should = Row 3 Balance / Row 2 Balance etc....

Table is just a Temporary table I created titled #MonthlyLoanBalance2. Now I just need to take it a step further. Let me know what and how you would go about doing this. Thank you in advance!

Insert into #MonthlytLoanBalance2 (
Year
,ProcessDate
,Month
,Balance
,RowNum
)
select 
--CloseYearMonth,
left(ProcessDate,4) as 'Year',
ProcessDate,
--x.LOANTypeKey,
SUBSTRING(CAST(x.ProcessDate as varchar(38)),5,2) as 'Month',
sum(x.currentBalance) as Balance
,ROW_NUMBER()over (order by ProcessDate desc) as RowNum
from
(
select 
distinct LoanServiceKey,
LoanTypeKey,
AccountNumber,
CurrentBalance,
OpenDateKey,
CloseDateKey,
ProcessDate


from
cu.LAFactLoanSnapShot
where LoanStatus = 'Open'
and LoanTypeKey = 0
and ProcessDate in (select DateKey from dimDate
where IsLastDayOfMonth = 'Y'
and DateKey > convert(varchar, getdate()-4000, 112)

)

) x

group by ProcessDate
order by ProcessDate desc;``

CodePudding user response:

I am assuming your data is already prepared as shown in the table. Now you can try Lead() function to resolve your issue. Remember format() function is used for taking only two precision.

SELECT *,
    FORMAT((ISNULL(LEAD(Balance,1) OVER (ORDER BY RowNum), 1)/Balance),'N2') Calculation
FROM #MonthlytLoanBalance2
  • Related