• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 200
  • Last Modified:

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

0
lrbrister
Asked:
lrbrister
  • 7
  • 7
  • 5
  • +1
3 Solutions
 
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
 
lrbristerAuthor 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
 
Kevin CrossChief 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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
 
anillucky31Commented:
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
 
Kevin CrossChief 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
 
lrbristerAuthor 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
 
lrbristerAuthor 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
 
lrbristerAuthor 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
 
lrbristerAuthor 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
 
lrbristerAuthor 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
 
lrbristerAuthor Commented:
very nice
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now