I have a table called Loan and have columns with percentage and amount. Eg :
ID Percentage Amount
1 10 10000
2 10 10000
3 15 15000
4 10 10000
The total loan approved for customer is 25%.
So I want to write a query to only display the lines from the table where percentage sum till reached 25%. The query should return ID 1 2 and 3 only. How to do it?
I write until this but return error :
Declare @PurchaserLoanAmount Integer = 25
Select Percentage, Amount
Where Sum(StagePercentage) <= @PurchaserLoanAmount
Order By Stage
Answer by anthony.green ·
A couple of points in the query, what is "StagePercentage"? you only have percentage
What is Stage? You don't have stage in the sample data ( I suspect that this is the ID column).
It sounds like you need a running total query on the percentage but then if the allowed percentage is 25, why would you allow them to have 35% of the loan if you include IDs 1,2,3
A rather basic example would be something like the below
create table #loan (id int, percentage int, amount int) insert into #loan values (1, 10, 10000), (2, 10, 10000), (3, 15, 15000), (4, 10, 10000) declare @LoanApprovedAmount INT = 25, @CurrentStageForAmount INT ;with cteLoan as ( select * ,sum(percentage) over (order by id) as runningpercent from #loan ) select @CurrentStageForAmount = min(id) from cteLoan where runningpercent >= @LoanApprovedAmount SELECT * FROM #loan where ID <= @CurrentStageForAmount