March 28, 2014 at 3:30 pm
Table is setup like this. My job is figure out all the item/records before it exceeds to some threshold amount. Lets say my
threshold amount is 8mil, I need to include only the records from the list where the total of the amount from this
table doesn't exceed 8mil. Any ideas?
ItemAmount
106261746525.4700000
106261846525.4700000
106261946525.4700000
106262046525.4700000
106262146525.4700000
106262246525.4700000
106262346525.4700000
106262446525.4700000
106262546525.4700000
106262646525.4700000
106262746525.4700000
106262846525.4700000
106262946525.4700000
106263046525.4700000
106263146525.4700000
106263246525.4700000
106263346525.4700000
106263446525.4700000
106263546525.4700000
106263646525.4700000
106263746525.4700000
106263846525.4700000
106263946525.4700000
106264046525.4700000
106264146525.4700000
106264246525.4700000
March 28, 2014 at 4:16 pm
Ignoring any potential performance issues, a CROSS APPLY can do what you want fairly easily. Simply indexing the table properly can often deal with that. Or you can use a "quirky update" variant if you really need max speed:
SELECT td.Item, td.Amount, ca1.running_total --,...
FROM table_data td
CROSS APPLY (
SELECT SUM(Amount) AS running_total
FROM table_data td2
WHERE
td2.Item <= td.Item
) AS ca1
WHERE
ca1.running_total <= 800000 --changed to 800,000 so the total would be reached
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 28, 2014 at 11:06 pm
This works perfect. Can you explain what this is doing? I'm new to cross apply.
March 29, 2014 at 6:44 am
SQL_Surfer (3/28/2014)
This works perfect. Can you explain what this is doing? I'm new to cross apply.
ScottPletcher (3/28/2014)
Ignoring any potential performance issues, a CROSS APPLY can do what you want fairly easily. Simply indexing the table properly can often deal with that. Or you can use a "quirky update" variant if you really need max speed:
SELECT td.Item, td.Amount, ca1.running_total --,...
FROM table_data td
CROSS APPLY (
SELECT SUM(Amount) AS running_total
FROM table_data td2
WHERE
td2.Item <= td.Item
) AS ca1
WHERE
ca1.running_total <= 800000 --changed to 800,000 so the total would be reached
For each row in table_data the subquery in the cross apply is processed generating a running total based on the criteria provided.
This is however not a very scalable solution as it is a triangular join and has the possibility of being less efficient than even a cursor.
Since you are using SQL Server 2008 I would like to suggest that you read the following article:
Solving the Running Total and Ordinal Rank Problems (Rewritten)[/url]
Be sure to read the discussion that goes with the article.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply