May 1, 2011 at 10:20 pm
Source table is something like:
Order Amount
--------------
order1 $2,000
order2 $1,000
order3 $3,000
order4 $500
order4 $600
order6 $4,000
Now because a manager has a signing authority of max $3,000 for a partition of orders, the query has to partition the orders in tiles having the totals as close as $3,000, but never more than this amount, something like this:
Order Amount Set
---------------------
order1 $2,000 1
order2 $1,000 1
order3 $3,000 2
order4 $500 3
order4 $600 3
order6 $3,000 4
Max value of amount possible is $3,000, so each tile will have at least 1 row. The whole set is ordered by date (or order #, that would be the same)
Now, of course this is completely doable with a cursor, but is there any way of doing this with just a query? Performance is not an issue (I feel the cursor solution would be faster here), the limitation is that I would just prefer a SELECT here ...
May 1, 2011 at 11:25 pm
I couldnt get the logic behind the grouping.. can you please be more elaborate on your explanation?? This certainly is do-able with set-based code..
May 1, 2011 at 11:30 pm
I don't believe this should be done with set-based coding. You are essentially slicing up an ordered set of row into 3k chunks ( or "tiles" as you called them ) and I don't think you will find a performant solution in a single query. Either a cursor solution or perhaps a quirky update appears to be in order, but I would be interested in seeing any solutions.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
May 1, 2011 at 11:32 pm
I just have to partition (not group) the orders in partitions that have the total amount of a partition of maximum $3,000.
May 1, 2011 at 11:34 pm
Performance is not an issue in this case, I am just curious if just a set based solution is possible for this
May 1, 2011 at 11:40 pm
Got it; Now, as Dixie says, either Quirky Update or Cursor can save you. I am still pondering if it will be possible by a Recursive-CTE..Got to think..
May 2, 2011 at 1:26 am
ColdCoffee (5/1/2011)
Got it; Now, as Dixie says, either Quirky Update or Cursor can save you. I am still pondering if it will be possible by a Recursive-CTE..Got to think..
A recursive CTE would work just fine for this. Quirky Update would be faster.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 2, 2011 at 1:51 am
virgilrucsandescu (5/1/2011)
Performance is not an issue in this case, I am just curious if just a set based solution is possible for this
This is probably the simplest set-based solution. It's certainly not going to be the fastest but should complete a million rows in less than a minute.
-- make up some sample data
DROP TABLE #Temp
CREATE TABLE #Temp (OrderNo VARCHAR(10), Amount MONEY)
INSERT INTO #Temp (OrderNo, Amount)
SELECT 'order1', 2000 UNION ALL
SELECT 'order2', 1000 UNION ALL
SELECT 'order3', 3000 UNION ALL
SELECT 'order4', 500 UNION ALL
SELECT 'order4', 600 UNION ALL
SELECT 'order6', 4000
-- reformat the sample data to make it more suitable for rCTE running total
DROP TABLE #OrderedSet
SELECT rn = ROW_NUMBER() OVER(ORDER BY OrderNo, Amount),
OrderNo, Amount
INTO #OrderedSet
FROM #Temp
ORDER BY OrderNo, Amount
CREATE UNIQUE CLUSTERED INDEX IXC_rn ON #OrderedSet (rn)
-- solution
;WITH Calculator AS (
SELECT rn,
OrderNo,
Amount,
RTAmount = Amount,
[Set] = 1
FROM #OrderedSet
WHERE rn = 1
UNION ALL
SELECT ThisRow.rn,
ThisRow.OrderNo,
ThisRow.Amount,
RTAmount = CASE WHEN ThisRow.Amount + LstRow.RTAmount <= 3000 THEN ThisRow.Amount + LstRow.RTAmount ELSE ThisRow.Amount END,
[Set] = CASE WHEN ThisRow.Amount + LstRow.RTAmount <= 3000 THEN LstRow.[Set] ELSE LstRow.[Set] + 1 END
FROM #OrderedSet ThisRow
INNER JOIN Calculator LstRow ON LstRow.rn+1 = ThisRow.rn
)
SELECT * FROM Calculator
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 2, 2011 at 8:38 am
Thank you, tt works just perfect!
Excuse my ignorance, but what's a "Quirky Update" ?
May 2, 2011 at 8:57 am
Check these threads on MSDN
MSDN thread with many helpful links
Lightning Fast Hybrid RUNNING TOTAL - Can you slow it down?[/url]
Wondering if the term 'Quirky Update' is Jeff Moden's invention.
May 2, 2011 at 9:32 am
virgilrucsandescu (5/2/2011)
Thank you, tt works just perfect!Excuse my ignorance, but what's a "Quirky Update" ?
Hi Virgil
This link[/url] will show you an alternative set-based method of calculating running totals and introduce you to the controversial "quirky update". It's much faster than the method I posted above but rather less intuitive to code, and - according to some - undocumented, however it works just fine in SQL Server 7 / 2000 / 2005 / 2008.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 3, 2011 at 7:05 am
ChrisM@home (5/2/2011)
virgilrucsandescu (5/2/2011)
Thank you, tt works just perfect!Excuse my ignorance, but what's a "Quirky Update" ?
Hi Virgil
This link[/url] will show you an alternative set-based method of calculating running totals and introduce you to the controversial "quirky update". It's much faster than the method I posted above but rather less intuitive to code, and - according to some - undocumented, however it works just fine in SQL Server 7 / 2000 / 2005 / 2008.
NOTE: it "only works just fine" if you follow numerous requirements EXACTLY TO THE LETTER!! And even then Microsoft has explicitly stated that it is an unsupported operation that is NOT guaranteed to work either now or in the future!!
What WILL make a HUGE difference for running totals is when Microsoft FINALLY implements full Windowing Function support in the engine. I sure hope that gets released in Denali!! That is by far the biggest shortcoming SQL Server has IMNSHO.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 12, 2011 at 2:47 pm
IMNSHO
😀
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 15, 2011 at 5:01 pm
ChrisM@home (5/2/2011)
This is probably the simplest set-based solution.
Oh... be careful, Chris. Apologies for quibbling but a Recursive CTE isn't set-based. It's a form of hidden RBAR which can sometimes be worse than a WHILE loop. It IS a simple solution but it's not a Set-Based one.
--Jeff Moden
May 15, 2011 at 5:14 pm
TheSQLGuru (5/3/2011)
That is by far the biggest shortcoming SQL Server has IMNSHO.
Unless you count the fact that there is no split function, no function to generate sequential numbers or dates on the fly, no method to do a Bulk Export directly from T-SQL, the fact that PIVOT sucks compared to the PIVOT available even in Access, that no function that approaches the utility of the deprecated sp_MakeWebTask exists, or the fact that they removed the functionality of the old F4 key, or the fact that you can't work in SSMS while the query designer is open and you can print from it, or the fact that neither BCP or BULK INSERT will successfully skip rows unless they have exactly the same number and type of delimiters, or that T-SQL has no file handling capabilities, or the fact that RAND produces the same value across multiple rows, or that... etc, etc, etc. 😉
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply