January 4, 2009 at 1:02 pm
I'm looking for a solution to find the maximum number of rows in one table which sum of their values is less than the value stated in a row in another table. I think the best way to explain this problem is by a simple example:
Consider a Items table as having the columns Id and ItemValue. The Id defines the sequence items were created. Easy. ItemValue is a value associated with the row.
Another table has to be considered: the Transactions table. The TId column defines the sequence transactions were created. The TranValue defines an amount that is gradually consumed by new items (in Items table).
Items table:
IdItemValue
1 2
21
33
42
51
Transactions table:
TIdTranValue
103
116
To find which items are consuming which transactions, we have to start looking from the highest Id's in both tables to the lowest.
Item 5 has value 1. TranValue of transaction 11 is 6. So 6 - 1 is higher than zero. Thus Item 5 is related with transaction 11.
Item 4 has value 2. So 6 - (1 + 2) is still higher than zero, making item 4 related with transaction 11 as well.
At the end you will see that Items 5, 4, and 3 are related with transaction 11 and Items 2 and 1 with transaction 10.
I could implement successfully this matching in a real system using (nested) cursors. They scan both tables making the sums and comparisons. However I was wondering if doesn't exist a better way to have this job done with even better performance. I have tried to use subqueries and group by, but couldn't figure out a way to make a "group by TId having TranValue < sum(ItemValue)" or something similar...
By the way, I'm using SQL Server 2000, but any solution for any SQL Server version is welcome.
January 4, 2009 at 2:23 pm
Essentially you are keeping a running total of the itemvalues and comparing it to the tranvalue. (Reading from last to first, as you said.)
Would it ever be possible for the running total of the itemvalues to be GREATER than the tranvalue? For an example, just change the itemvalue of the second row to be 2 instead of 1. Item 2 would be <= the tranvalue for transaction 10, but what would you do with item 1?
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 4, 2009 at 4:29 pm
In my case, not really because the Transaction table add some sort of credits that are taken by items; these credits can't be negative.
One more detail, you can consider as impossible for an item to take credits from more than one transaction. So Item 3 would never be greater than 3.
You got right, I need the running total of Value and compare with TranValue to track from which transaction came the values (using cursors its not that difficult, but the SQL looks ugly...
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply