April 14, 2010 at 7:07 am
DECLARE @amount float
SET @amount=9.33
DECLARE @table TABLE (ID int, Amount float)
INSERT INTO @table VALUES(1, 1.00)
INSERT INTO @table VALUES(2, 2.33)
INSERT INTO @table VALUES(3, 3.67)
INSERT INTO @table VALUES(4, 4.00)
INSERT INTO @table VALUES(5, 5.33)
INSERT INTO @table VALUES(6, 6.67)
INSERT INTO @table VALUES(7, 7.33)
INSERT INTO @table VALUES(8, 8.67)
INSERT INTO @table VALUES(9, 9.00)
SELECT ID,Amount FROM @table
WHERE SUM(Amount)=@amount
April 14, 2010 at 7:43 am
For the above scenario...U should use the loop [Cursor /Temp table/tbl variable] take the each row individually insert into new table if the SUM is below the Variable amount u had given....
Select that rows[which sum is equal or lessthan the amnt] inserted tbl.
:-):-)
April 14, 2010 at 7:50 am
I appreciate your quick response and offer of assistance, but I thought about doing that but the solution seems like it would be slow to get all the different combinations in the new table, and then select from that. Do you agree?
Is there a faster solution than that?
April 14, 2010 at 8:03 am
if you are trying to find the consecutive records from start to some row that equals the amount, this solution below using a "running total" works;
if you are after some sort of factorial solution, where anythign from 1 to n-1 records can consist of the total, i don't know how you'd handle that solution.
CREATE TABLE #table (ID int, Amount float)
INSERT INTO #table VALUES(1, 1.00)
INSERT INTO #table VALUES(2, 2.33)
INSERT INTO #table VALUES(3, 3.67)
INSERT INTO #table VALUES(4, 4.00)
INSERT INTO #table VALUES(5, 5.33)
INSERT INTO #table VALUES(6, 6.67)
INSERT INTO #table VALUES(7, 7.33)
INSERT INTO #table VALUES(8, 8.67)
INSERT INTO #table VALUES(9, 9.00)
DECLARE @amount float
SET @amount=9.33
SELECT * FROM (
select
Sub1.RW AS RW1,
Sub2.RW AS RW2,
Sub1.Amount AS Amount1,
Sub2.Amount AS Amount2,
Sub1.Amount + Sub2.Amount As TheTotal
from
(select row_number() over(order by ID) As RW ,Amount FROM #table) Sub1
left outer join
(select row_number() over(order by ID) As RW ,Amount FROM #table) Sub2
ON Sub1.RW + 1 = Sub2.RW
) x
WHERE TheTotal = @amount
Lowell
April 14, 2010 at 8:07 am
Thanks for the quick response, and yes, the solution could be 1-n.
April 14, 2010 at 8:10 am
hi
why not this
DECLARE @amount float
SET @amount=9
DECLARE @table TABLE (ID int, Amount float)
INSERT INTO @table VALUES(1, 1.00)
INSERT INTO @table VALUES(2, 2.33)
INSERT INTO @table VALUES(3, 3.67)
INSERT INTO @table VALUES(4, 4.00)
INSERT INTO @table VALUES(5, 5.33)
INSERT INTO @table VALUES(6, 6.67)
INSERT INTO @table VALUES(7, 7.33)
INSERT INTO @table VALUES(8, 8.67)
INSERT INTO @table VALUES(9, 9.00)
SELECT ID,Amount FROM @table
group by ID,Amount
having SUM(Amount)=@amount
April 14, 2010 at 8:19 am
I appreciate your response but that solution doesn't actually return the combinations because it groups by the ID.
April 14, 2010 at 8:24 am
hi
do you want running total??
April 14, 2010 at 8:27 am
Hey,
No, I'm looking for the combination of ID's whose corresponding amounts sum up to the value in the @amount variable. This could be just one row if it's a match or a combination of any number of rows so long as it adds up to the total.
April 14, 2010 at 8:29 am
hi
i think you got the answer from lowel..
April 14, 2010 at 8:47 am
I tried that solution and it seems close, but it only works for two contiguous rows. And doesn't support if 3 or more rows are what comprise the result.
April 14, 2010 at 8:49 am
do you want the first occurence of that sum in a running total?
April 14, 2010 at 8:51 am
I guess I'm unclear on what you mean by running total. If rows 2 and 7 add up to the value in the @amount variable, I want those ID's returned. If rows 1, 3, and 5 add up to the value in the @amount variable, I want those ID's returned.
April 14, 2010 at 9:06 am
it seems like you want a 1 to N solution, not a running total(since you explicitly gave the 1-3-5 example of non consecutive values)
for any actual solution, i would end up doing this in a programming language instead of trying it in TSQL;
the better question is, what are you really trying to do? are you reverse engineering some totals while missing invoice details or something?
what happens if two different factorials could produce the same value?
select 1.00 + 2.33 + 6.67 = 10
select 1.00 + 9.00 = 10
are your representative values are real or pseudocode? they seem incremental, was that just coincidence or is there a pattern? would a value in the real data repeat?(can there be two recs with 1.00?)
take the time to explain the purpose,as well as the structure of the data outside of the example; I think we could help you better. if we KNEW the values were unique, it might help.
Lowell
April 14, 2010 at 9:15 am
Ok - here is the business requirement. A customer sends a check to pay a bill or combination of bills. I want to determine which bills they meant to pay by seeing if there is a combination of bills that add up to the amount of the check.
The example was incremental and that was probably not the best selection of sample data as it will not likely be incremental.
If two different combinations do happen to match, I will use the combination that has the oldest bill in it.
I could accomplish this in C#, but I was hoping to allow the user to enter a number of bills into the system and then have a Sql Server stored procedure allocate the funds to the appropriate bills if it could.
I do appreciate everyone's efforts, thank you.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply