Select group of numbers < maxNumber in SQL Server

I have an unusual request from our financial department.

There are loans we need to collateralize and we do so from a list of assets.

These assets are valued from $1 to $1,500,000

The attached code kind of works...but I need it to keep grabbing values until it gets to a sum in the @a table that is as close to =  @ttlAmount that I can get

I need a "FETCH WHILE" as shown below...not sure how to go about it

I know...wierd request.
--set total amount needed
Declare @ttlAmount money
Set @ttlAmount = 500000

--declare base variables
Declare @afsID int
Declare @afsSource nvarchar(16)
Declare @afsAmount money
Declare @aSumAmount money

Select top 1 @afsID = afsid, @afsSource = afsSource, @afsAmount = afsAmount 
from proc_cfa.dbo.p_availableforsale
where afsAmount <= @ttlAmount and afsTransaction = 0
order by afsAmount desc, afsSource


--declare holding table
declare @a table (afsid int, afsSource nvarchar(16), afsAmount money)


--insert initial values into table variable
insert into @a
select @afsID, @afsSource, @afsAmount


--===========================================================
-- Need a fetch while  sum table @a.afsAmount < @ttlAmount here
--  Stop when the select is null
--============================================================
--set Sum amount variable from sum in table
Set @aSumAmount = (Select Sum(@afsAmount) from @a)

--get max amount from source that is <= @ttlAmount - @aSumAmount 
Select top 1 @afsID = afsid, @afsSource = afsSource, @afsAmount = afsAmount 
from proc_cfa.dbo.p_availableforsale
where afsAmount <= @ttlAmount - @aSumAmount and afsTransaction = 0
order by afsAmount desc, afsSource

--insert new value in table
insert into @a
select @afsID, @afsSource, @afsAmount


Select * from @a

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
anillucky31Connect With a Mentor Commented:
I have modified your code
i have added new variable
declare @LoopEnable int

set  @LoopEnable = 1

see attached code
--set total amount needed
Declare @ttlAmount money
Set @ttlAmount = 500000

--declare base variables
Declare @afsID int
Declare @afsSource nvarchar(16)
Declare @afsAmount money
Declare @aSumAmount money

declare @LoopEnable int

set  @LoopEnable = 1

Select top 1 @afsID = afsid, @afsSource = afsSource, @afsAmount = afsAmount 
from proc_cfa.dbo.p_availableforsale
where afsAmount <= @ttlAmount and afsTransaction = 0
order by afsAmount desc, afsSource


--declare holding table
declare @a table (afsid int, afsSource nvarchar(16), afsAmount money)


--insert initial values into table variable
insert into @a
select @afsID, @afsSource, @afsAmount

while @afsAmount < @ttlAmount and @LoopEnable = 1
begin
	Set @aSumAmount = (Select Sum(@afsAmount) from @a)
	
	if exists(select top 1 * from proc_cfa.dbo.p_availableforsale
	where afsAmount <= @ttlAmount - @aSumAmount and afsTransaction = 0
	order by afsAmount desc, afsSource)
	begin
		Select top 1 @afsID = afsid, @afsSource = afsSource, @afsAmount = afsAmount 
		from proc_cfa.dbo.p_availableforsale
		where afsAmount <= @ttlAmount - @aSumAmount and afsTransaction = 0
		order by afsAmount desc, afsSource
		
		insert into @a
		select @afsID, @afsSource, @afsAmount
	
	end
	else
	begin
	set  @LoopEnable = 0
	end
	
	

end
 
Select * from @a

Open in new window

0
 
aflockhartCommented:
If the values in your database are 7,6 and 5 ; and the target amount is 11, what would you expect to get as the answer ? If you start by adding the 7, you can't get any closer.  But if you start with the 6, you can add the 5 to hit the value exactly.

If you are looking for the latter, that will be hard.

 If you want the former, you may be able to set the @afsid variable to a fixed value that you know is not contained in the database  ( maybe -1 or similar).  Then after processing each item, check whether @afsid is still set to -1 ; if the last query returned no matchihg records, then it won;t update the variable value, and that tells you  it is time to stop.

For any large datasets, this is likely to be really slow.  
0
 
Larry Bristersr. DeveloperAuthor Commented:
aflockhart:
I would want to get back the 7 at this time.  They can then make a decision to use a partial amount of one of the others.

That probably isn't going to happen though

There will be a pool of amounts from $50 to 50,000 in general to choose from.

If their ttl needed is $510,000 it would probaly "fetch" a bacth of $50,000 records and then work its way down to something close..,then they would wonce again use a part of something manually as collateral.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Hi.

With SQL 2008, you could try a recursive common table expression or a subquery to keep running total if you have a good column to use as sequencing (i.e., you could get a different answer if you have 1M rows that are $1 each versus 10 rows that are $1M each, so where do you want to count from?).  Performance may not be the greatest, but it can be done.  For the CTE approach, the number of rows comes into play for max recursion.

For the CTE approach, you could first start with making a sequential id (no gaps) if one doesn't already exist by putting together your data with ROW_NUMBER() function to rank rows by your desired sort/partitioning.
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html

From there, you can use recursion in a subsequent CTE that starts with record(s) with row number = 1 and then recursing through rows to build your running total.  Now thinking about it more, you can use your limiting variable to test the running total as you recurse so that you are less likely to hit max recursion limit.

Specifies the maximum number of recursions allowed for this query. number is a non-negative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server is 100.
-- http://msdn.microsoft.com/en-us/library/ms181714.aspx

Let's take a look an example.  If it won't work in your scenario, we can talk further on the subquery approach.

;with ranked_cte as (
   select afsID, afsSource, afsAmount
        , row_number() over(order by afsAmount desc, afsSource) rn
   from proc_cfa.dbo.p_availableforsale
   where afsAmount <= @ttlAmount and afsTransaction = 0
), running_total_cte(afsID, afsSource, afsAmout, rn, rt) as (
   -- anchor query
   select afsID, afsSource, afsAmount, rn, afsAmount -- running total
   from ranked_cte
   where rn = 1

   union all

   -- recursive piece
   select a.afsID, a.afsSource, a.afsAmount, a.rn, a.afsAmount + b.rt
   from ranked_cte a
   join running_total_cte b on b.rn = a.rn-1
   -- added filter to avoid lengthy (unnecessary) recursion
   where a.afsAmount <= (@ttlAmount - b.rt)
)
-- final selection
select afsID, afsSource, afsAmount
from running_total_cte
;

Open in new window


Hope that helps!

Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry, it looks like a missed a brief conversation above.  I was typing and gathering references for you and so missed those posts.  If I need to adjust anything based on that, please advise.  I will read those comments and reply if it answers questions I had.
0
 
Kevin CrossChief Technology OfficerCommented:
Per your other discussion, if they ever wanted to automate the selection of the next row to take partial from, you could simply alter:

where a.afsAmount <= (@ttlAmount - b.rt)

to:

where b.rt < @ttlAmount

Since that checks the running total is less than the desired value before taking into account the new amount/record, then you will always get one extra row.  You can add another column to the CTE that is balance, that is essentially (@ttlAmount - b.rt - a.afsAmount) and so you should have x number of rows where this balance is positive with final row where it is 0 or negative reflecting portion that will be remaining after partial is taken.

Otherwise, as written, my code should give you 7 in your scenario as I went with ordering by the highest amount descending and am checking that the new running total is at or below total desired; therefore, the recursion should break on first row.

I didn't have your data to test, but syntax should be pretty straight-forward; however, please let me know if I made any type-o's or logical slips.
0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
If you choose to do it in this fashion, please take note of the following:

Set @aSumAmount = (Select Sum(@afsAmount) from @a)
should be:
Set @aSumAmount = (Select Sum(afsAmount) from @a)

I would not use variables for @afsID, @afsSource and @afsAmount, but instead insert directly to your table:

--insert initial values into table variable
insert into @a(afsID, afsSource, afsAmount)
Select top 1 afsid, afsSource, afsAmount
from proc_cfa.dbo.p_availableforsale
where afsAmount <= (@ttlAmount - coalesce(@aSumAmount, 0))
and afsTransaction = 0
order by afsAmount desc, afsSource
;

while @afsAmount < @ttlAmount and @LoopEnable = 1
should be:
while (@LoopEnable = 1)
or:
while (@aSumAmount < @ttlAmount)

Having both is redundant and having @afsAmount is incorrect as it is not taking into account the running total making loop need the @LoopEnable.  Correct that and my comment on redundancy will become clearer.

There are a few other little things, but after cleaning above they probably become moot in the new design.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Hey guys...looks like your collaberation worked and some great input together.

Final working copy attached.

But mwvisa1: was invaluable.

Any problem with a split of some kind?
--set total amount needed  
Declare @ttlAmount money  
Set @ttlAmount = 510301  
  
--declare base variables  
Declare @afsID int  
Declare @afsSource nvarchar(16)  
Declare @afsAmount money  
Declare @aSumAmount money  
  
declare @LoopEnable int  
  
set  @LoopEnable = 1  
  
Select top 1 @afsID = afsid, @afsSource = afsSource, @afsAmount = afsAmount   
from proc_cfa.dbo.p_availableforsale  
where afsAmount <= @ttlAmount and afsTransaction = 0  
order by afsAmount desc, afsSource  
  
  
--declare holding table  
declare @a table (afsid int, afsSource nvarchar(16), afsAmount money)  
  
  
--insert initial values into table variable  
insert into @a  
select @afsID, @afsSource, @afsAmount  
  
while @afsAmount < @ttlAmount and @LoopEnable = 1  
begin  
        Set @aSumAmount = (Select Sum(afsAmount) from @a)  
          
        if exists(select top 1 * from proc_cfa.dbo.p_availableforsale  
        where afsAmount <= @ttlAmount - @aSumAmount and afsTransaction = 0  
        order by afsAmount desc, afsSource)  
        begin  
                Select top 1 @afsID = afsid, @afsSource = afsSource, @afsAmount = afsAmount   
                from proc_cfa.dbo.p_availableforsale  
                where afsAmount <= @ttlAmount - @aSumAmount and afsTransaction = 0  
                order by afsAmount desc, afsSource  
                  
                insert into @a  
                select @afsID, @afsSource, @afsAmount  
          
        end  
        else  
        begin  
        set  @LoopEnable = 0  
        end  
end  


--Compare requested toal vs. ttlAmount in @a
Select @ttlAmount, SUM(afsAmount) ttlA_Amount from @a

--==================================================================
--See if anything was missed by seeing what amount in source table 
--  exists that COULD be pulled and wasn't
Declare @ttlFinal money
Select @ttlFinal = (Select SUM(afsAmount) from @a)

Select * from proc_cfa.dbo.p_availableforsale 
where afsAmount < (@ttlAmount - @ttlFinal) and afsTransaction = 0
--====================================================================

Open in new window

0
 
anillucky31Commented:
mwvisa1 we  can directly insert into table but we need to retrieve afsAmount from table and we have to uses it while condition. So we have to use @afsAmount. So this variable is must
--set total amount needed
Declare @ttlAmount money
Set @ttlAmount = 500000

--declare base variables
Declare @afsID int
Declare @afsSource nvarchar(16)
Declare @afsAmount money
Declare @aSumAmount money

declare @LoopEnable int

set  @LoopEnable = 1

Select top 1 @afsID = afsid, @afsSource = afsSource, @afsAmount = afsAmount 
from proc_cfa.dbo.p_availableforsale
where afsAmount <= @ttlAmount and afsTransaction = 0
order by afsAmount desc, afsSource


--declare holding table
declare @a table (afsid int, afsSource nvarchar(16), afsAmount money)


--insert initial values into table variable
insert into @a
select @afsID, @afsSource, @afsAmount

while @afsAmount < @ttlAmount and @LoopEnable = 1
begin
	Set @aSumAmount = (Select Sum(afsAmount) from @a)
	
	if exists(select top 1 * from proc_cfa.dbo.p_availableforsale
	where afsAmount <= @ttlAmount - @aSumAmount and afsTransaction = 0
	order by afsAmount desc, afsSource)
	begin
		Select top 1 @afsID = afsid, @afsSource = afsSource, @afsAmount = afsAmount 
		from proc_cfa.dbo.p_availableforsale
		where afsAmount <= @ttlAmount - @aSumAmount and afsTransaction = 0
		order by afsAmount desc, afsSource
		
		insert into @a
		select @afsID, @afsSource, @afsAmount
	
	end
	else
	begin
	set  @LoopEnable = 0
	end
	
	

end
 
Select * from @a

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
anillucky31:
Not sure what I'm missing...because my example above works.  Are you saying there are conditions where it will break?
0
 
anillucky31Commented:
lrbrister your final code will work. i was trying to explain mwvisa1 that why i need variables @afsAmount. I see no problem with your code now
0
 
Larry Bristersr. DeveloperAuthor Commented:
anillucky31:

Cool...you both had some great input and even some collaberation.

Any problem on your side with a split?
0
 
anillucky31Commented:
No problem
0
 
Larry Bristersr. DeveloperAuthor Commented:
Absolutely excellent work!

Thanks folks.  Invalueable examples and assistance.

From my perspective...this is exactly how EE should work.
0
 
Kevin CrossChief Technology OfficerCommented:
Split is fine by me.

@anillucky31, no it is not.  Since you have added a LoopEnable flag then it is redundant and wrong even to use @afsAmount in the WHILE loop.  The whole point here is that any one afsAmount, which is what is stored in that variable, is less than the @ttlAmount requested in the first place; therefore, it is not doing anything of importance in the loop.  Once inside the loop, if the new running total @aSumAmount is going to over the @ttlAmount then @LoopEnable can be set to 0 and as such stop the while loop.

Given the amount of data we are dealing with, efficiency is important.  Equally so in my humble opinion is maintainability of the code.  
0
 
Larry Bristersr. DeveloperAuthor Commented:
Good followup up guys.

You folsk are way past me in this...thanks for the help
0
 
anillucky31Commented:
You are right mwvisa1. Good point.
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome, @lrbrister.
@anillucky31, good job on solution by the way.  By your last comment, I am hoping you knew the discussion was to help and not meant negatively.

Here is an example, full code:
--set total amount needed  
Declare @ttlAmount Money  
Set @ttlAmount = 510301;
  
--declare base variables  
Declare @aSumAmount Money  
Set @aSumAmount = 0;

--declare holding table  
declare @a table (afsid int, afsSource nvarchar(16), afsAmount money);

While Exists(
   Select 1 
   From proc_cfa.dbo.p_availableforsale
   Where afsAmount <= (@ttlAmount - @aSumAmount)
   And afsTransaction = 0 
) Begin
   -- insert row that meets criteria
   Insert Into @a 
   Select Top 1 afsid, afsSource, afsAmount
   From proc_cfa.dbo.p_availableforsale
   Where afsAmount <= (@ttlAmount - @aSumAmount)
   And afsTransaction = 0 
   Order By afsAmount Desc, afsSource
   ;

   -- update new running total
   Select @aSumAmount = Sum(afsAmout) 
   From @a;
End

--Compare requested toal vs. ttlAmount in @a
Select @ttlAmount, SUM(afsAmount) ttlA_Amount from @a

--==================================================================
--See if anything was missed by seeing what amount in source table 
--  exists that COULD be pulled and wasn't
Select afsid, afsSource, afsAmount
From proc_cfa.dbo.p_availableforsale 
Where afsAmount <= (@ttlAmount - @aSumAmount)
And afsTransaction = 0
--====================================================================

Open in new window


Anyway, as I said, glad we could help.

Best regards and happy coding,
Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
All, sorry for the extra e-mail, but there is a flaw in this method that I didn't think about given my original approach uses row numbering and so is sequentially checked.  In this scenario, we are always taking TOP 1 row from your data that is below the @ttlAmount less the current @aSumAmount.  In theory this works as you get a row with smaller amount matching each time; however, as you stated, you may have $500k to start and pull $50k as first row.  That same $50k row meets the new <= $450 criteria and may be selected again.

Here is corrected code:
--set total amount needed  
Declare @ttlAmount Money  
Set @ttlAmount = 510301;
  
--declare base variables  
Declare @aSumAmount Money  
Set @aSumAmount = 0;

--declare holding table  
declare @a table (afsid int, afsSource nvarchar(16), afsAmount money);

While Exists(
   Select 1 
   From proc_cfa.dbo.p_availableforsale a
   Where afsAmount <= (@ttlAmount - @aSumAmount) And afsTransaction = 0 
   -- check that row hasn't already been used
   -- add to WHERE clause if afsid doesn't uniquely identify a row
   And Not Exists (Select 1 From @a b Where b.afsid = a.afsid)
) Begin
   -- insert row that meets criteria
   Insert Into @a 
   Select Top 1 afsid, afsSource, afsAmount
   From proc_cfa.dbo.p_availableforsale a
   Where afsAmount <= (@ttlAmount - @aSumAmount) And afsTransaction = 0 
   And Not Exists (Select 1 From @a b Where b.afsid = a.afsid) -- set criteria same as WHILE condition
   Order By afsAmount Desc, afsSource
   ;

   -- update new running total
   Select @aSumAmount = Sum(afsAmout) From @a;
End

--Compare requested toal vs. ttlAmount in @a
Select @ttlAmount, SUM(afsAmount) ttlA_Amount from @a

--==================================================================
--See if anything was missed by seeing what amount in source table 
--  exists that COULD be pulled and wasn't
Select afsid, afsSource, afsAmount
From proc_cfa.dbo.p_availableforsale 
Where afsAmount <= (@ttlAmount - @aSumAmount) And afsTransaction = 0
And Not Exists (Select 1 From @a b Where b.afsid = a.afsid) -- set criteria same as WHILE condition
--====================================================================

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
very nice
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.