I have a table where like this.

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