SELECT nearest under or equal to X based on SUM amount SQL Server -

i have 2 tables:

declare @tbitems table (accntno varchar, saved_amount decimal) insert @tbitems select 001     ,  25  declare @tbtransact table (idno int , acctno varchar, amount decimal) insert @tbtransact select 1 , 001 ,  10 union select 2 , 001 ,  10 union select 3 , 001 ,  10 union select 4 , 001 ,  10 

tbitems:

accntno | saved_amount (decimal) --------+----------------------- 001     |  25 

tbtransact:

idno  | acctno  | amount (decimal) ------+---------+----------------- 1     | 001     |  10 2     | 001     |  10 3     | 001     |  10 4     | 001     |  10 

how nearest idno less or equal saved_amount tbitems adding tbtransact amounts ordered idno (i don't know how in words).

anyway based on table, expected result should idno 2 since nearest under 25 20

if in java, loop through tbtransact add every row until go higher saved_amount idno before current one. have no idea how in sql.

expected result is:

idno  | acctno  | amount ------+---------+----------------- 2     | 001     |  10 

this query works selecting correct row specific account:

select top 1         t.idno ,         s.*    dbo.tbitems         inner join dbo.tbtransact t on i.accntno = t.acctno         cross apply ( select    sum(l.amount) sumamount                            dbo.tbtransact l                           l.acctno = t.acctno                                 , l.idno <= t.idno                     ) s   i.accntno = '001' , s.sumamount <= i.saved_amount order s.sumamount desc; 

and should word accross multipse accounts:

select t.acctno, max(idno) idno, max(s.sumamount) sumamount    dbo.tbitems         inner join dbo.tbtransact t on i.accntno = t.acctno         cross apply ( select    sum(l.amount) sumamount                            dbo.tbtransact l                           l.acctno = t.acctno                                 , l.idno <= t.idno                     ) s   s.sumamount <= i.saved_amount group t.acctno 

here example using cte , windowing function:

with    cte1           ( select   t.acctno ,                         t.amount ,                         sum(t.amount) on ( partition t.acctno order t.acctno, t.idno       rows between unbounded preceding , current row ) runningtotal                    dbo.tbtransact t                         inner join dbo.tbitems on t.acctno = i.accntno              ),         cte2           ( select   row_number() on ( partition cte1.acctno order cte1.runningtotal desc ) rn ,                         cte1.acctno ,                         cte1.amount ,                         cte1.runningtotal                    cte1              )     select  cte2.acctno ,             cte2.amount ,             cte2.runningtotal        cte2       cte2.rn = 1; 

wiki