SQLServerCentral.com / SQL Server 2005 / T-SQL (SS2K5) / Filling Buckets / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 21 Oct 2016 05:57:55 GMT20RE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]ashishkumarrai (9/14/2016)[/b][hr][quote][b]J Livingston SQL (9/13/2016)[/b][hr][quote][b]ashishkumarrai (9/13/2016)[/b][hr]It seems this is not what I am after. I want the filler data to feed to bucket in recursive way and store each recursion. the output should look like:After 1st iterationBucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 852 | 80 | 5 3 | 75 | 04 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------After second iterationBucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 02 | 80 | 45 3 | 75 | 04 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------After third iteration and so on......BucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 02 | 80 | 803 | 75 | 354 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------[/quote]sorry...but at the moment i am at a loss as to why you wish to store each iteration....can you please explain the business requriement behind this?[/quote]Sure, actually it is a matrix. I want to preserve the matrix and store each cell as there are calculation to be done on each cell. Imagine it as a matrix where bucket table is on X axis and Filler table is on Y axis. After each iteration we will fill one or more cell. hope that explains.[/quote]What would certainly help would be a data structure with before and after figures. The post above shows the same rows & columns of the same table processed in cycles. If this is what you want, then it's back to the original, looping through the values in the filler table.Wed, 14 Sep 2016 02:34:36 GMTChrisM@WorkRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]J Livingston SQL (9/13/2016)[/b][hr][quote][b]ashishkumarrai (9/13/2016)[/b][hr]It seems this is not what I am after. I want the filler data to feed to bucket in recursive way and store each recursion. the output should look like:After 1st iterationBucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 852 | 80 | 5 3 | 75 | 04 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------After second iterationBucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 02 | 80 | 45 3 | 75 | 04 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------After third iteration and so on......BucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 02 | 80 | 803 | 75 | 354 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------[/quote]sorry...but at the moment i am at a loss as to why you wish to store each iteration....can you please explain the business requriement behind this?[/quote]Sure, actually it is a matrix. I want to preserve the matrix and store each cell as there are calculation to be done on each cell. Imagine it as a matrix where bucket table is on X axis and Filler table is on Y axis. After each iteration we will fill one or more cell. hope that explains.Wed, 14 Sep 2016 02:23:36 GMTashishkumarraiRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxThis is a direct cut and paste from "SQL for Smarties", you will have to figure out where the data in the tables and stuff should go. But it looks like what you are after is a T join. This does not require any of the window functions. We have now so will probably run in SQL-2008. 26.07. Dr. Codd's T-JoinIn the Second Version of the relational model in 1990, Dr. E. F. Codd introduced a set of new theta operators, called T-operators, which were based on the idea of a best-fit or approximate equality (Codd 1990). The algorithm for the operators is easier to understand with an example modified from Dr. Codd.The problem is to assign the classes to the available classrooms. We want (class_size < room_size) to be true after the assignments are made. This will allow us a few empty seats in each room for late students. We can do this in one of two ways. The first way is to sort the tables in ascending order by classroom size and the number of students in a class. We start with the following tables and load them with the data that follows the DDL. CREATE TABLE Rooms(room_nbr CHAR(3) NOT NULL PRIMARY KEY, room_size INTEGER NOT NULL);Classesclass_nbrclass_size'c1'80'c2'70'c3'65'c4'55'c5'50'c6'40CREATE TABLE Classes(class_nbr CHAR(3) NOT NULL PRIMARY KEY, class_size INTEGER NOT NULL);Roomsroom_nbrroom_sizer170r240r350r485r530r665r755The goal of the T-JOIN problem is to assign a class which is smaller than the classroom given it (class_size < room_size). Dr. Codd gives two approaches to the problem.1) Ascending Order Algorithm Sort both tables into ascending order. Reading from the top of the Rooms table, match each class with the first room that will fit.Classes class_nbrclass_sizec640c550c455c365c270c180 Roomsroom_nbrroom_sizer530r240r350r755r665r170r485This gives usResultsClassesRoomsclass_nbrclass_sizeroom_nbrroom_sizec270r485c365r170c455r665c550r755c640r3502) Descending Order AlgorithmSort both tables into descending order. Reading from the top of the Classes table, match each class with the first room that will fit.ClassesRoomsclass_nbrclass_sizeroom_nbrroom_sizec180r485c270r170c365r665c455r755c550r350c640r240NULLNULLr530Results class_nbrclass_sizeroom_nbrroom_sizec180r485c365r170c455r665c550r755c640r3'50Notice that the answers are different! Dr. Codd has never given a definition in relational algebra of the T-Join, so I proposed that we need one. Informally, for each class, we want the smallest room that will hold it, while maintaining the T-JOIN condition. Or for each room, we want the largest class that will fill it, while maintaining the T-JOIN condition. These can be two different things, so you must decide which table is the driver. But either way, I am advocating a "best fit" over Codd's "first fit" approach.Other theta conditions can be used in place of the "less than" shown here. If "less than or equal" is used, all the classes are assigned to a room in this case, but not in all cases. This is left to the reader as an exercise.The first attempts in Standard SQL are versions of grouped by queries. They can, however, produce some rows that would be left out of the answers Dr. Codd was expecting. The first JOIN can be written asSELECT class_nbr, class_size, MIN(room_size) FROM Rooms, Classes WHERE Classes.class_size < Rooms.room_size GROUP BY class_nbr, class_size;This will give a result table with the desired room sizes, but not the room numbers. You cannot put the other columns in the SELECT list, since it would conflict with the GROUP BY clause. But also note that the classroom with 85 seats ('r4') is used twice, once by class 'c1' and then by class 'c2':class_sizeclass_sizeMIN(room_size)Notesc18085◄ room r4c27085◄ room r4c36570c45565c55055c64050If you do a little arithmetic on the data, you find that we have 360 students and 395 seats, 6 classes and 7 rooms. Do you want to use the smallest number of rooms? As it works out, the best fit of rooms to classes will leave the smallest room empty and pack the other rooms to capacity, thus:SELECT class_nbr, class_size, MIN(room_size) FROM Rooms, Classes WHERE Classes.class_size <= Rooms.room_size GROUP BY class_nbr, class_size;26.07.01. A Procedural Approach The place to start is with all the possible legal room assignments for a class. We have already seen this query: SELECT R.room_nbr, C.class_nbr FROM Rooms AS R, Classes AS C WHERE C.class_size <= R.room_size ORDER BY R.room_nbr, C.class_nbr;At the extreme, if all the rooms and classes are the same size, then your have (n!) solutions. If all the rooms are different sizes, we can save ourselves a combinatorial explosions, so let us agree that we will juggle the data to get that condition. This query will give us the pairs that are an exact fit, but we know that there will not be any ties for room size. SELECT R.room_nbr, C.class_nbr FROM Rooms AS R, Classes AS C WHERE C.class_size <= R.room_size GROUP BY R.room_nbr, C.class_nbrHAVING MIN(R.room_size - C.class_size) = 0;r1c2r6c3r7c4r3c5r2c6This leaves us with class {c1} and rooms {r4, r5} yet to be used. We can then repeat a limited version of the basic Pairs query. SELECT R.room_nbr, C.class_nbr FROM Rooms AS R, Classes AS C WHERE C.class_size <= R.room_size AND R.room_nbr IN ('r4', 'r5') AND C.class_nbr IN ('c1');r4c1We can now union these result sets and have an answer. I took some extra time to show the details to demonstrate how we can implement a best-fit, greedy algorithm. Let us get a bigger data set and work with it. INSERT INTO Classes (class_nbr, class_size)VALUES ('c01', 106), ('c02', 105), ('c03', 104), ('c04', 100), ('c05', 99), ('c06', 90), ('c07', 89), ('c08', 88), ('c09', 83), ('c10', 82), ('c11', 81), ('c12', 65), ('c13', 50), ('c14', 49), ('c15', 30), ('c16', 29), ('c17', 28), ('c18', 20), ('c19', 19);INSERT INTO Rooms (room_nbr, room_size)VALUES ('r01', 102), ('r02', 101), ('r03', 95), ('r04', 94), ('r05', 85), ('r06', 70), ('r07', 55), ('r08', 54), ('r09', 35), ('r10', 34), ('r11', 25), ('r12', 18);To see how this will work, let's add another column to the table of legal (class_nbr, room_nbr) pairs to see how well they fit. WITH Pairs (room_nbr, class_nbr, fit)AS (SELECT R.room_nbr, C.class_nbr, (R.room_size - C.class_size) FROM Rooms AS R, Classes AS C WHERE C.class_size <= R.room_size) SELECT P1.room_nbr, P1.class_nbr, fit FROM Pairs AS P1 WHERE P1.fit = (SELECT MIN(P2.fit) FROM Pairs AS P2 WHERE P1.room_nbr = P2.room_nbr);r01c042r02c041r03c065r04c064r05c092r06c125r07c135r08c134r09c155r10c154r11c185This time we did not have an exact fit, so let's look for (fit = 1) into a working table and remove 'r02' and 'c04' from their tables. The second step is r02c041We can now remove the best fit rooms and classes, and look the next set of remaining best fits. WITH Pairs (room_nbr, class_nbr, fit)AS (SELECT R.room_nbr, C.class_nbr, (R.room_size - C.class_size) FROM Rooms AS R, Classes AS C WHERE C.class_size <= R.room_size)(SELECT P1.room_nbr, P1.class_nbr, fit FROM Pairs AS P1 WHERE P1.fit = 2);r05c092Here is the step for a fit of 3, 4, 5 and 6r01c053r04c064r08c134r10c154r06c125r11c185r03c076r07c146r09c166At this point, the original tables of rooms and classes cannot be paired. Notice that as we removed rooms and classes, the fit numbers changed. This is a characteristic of greedy algorithms; taking the “big bites” can leave sub-optimal leftovers on the plate. Tue, 13 Sep 2016 12:06:50 GMTCELKORE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]ashishkumarrai (9/13/2016)[/b][hr]It seems this is not what I am after. I want the filler data to feed to bucket in recursive way and store each recursion. the output should look like:After 1st iterationBucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 852 | 80 | 5 3 | 75 | 04 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------After second iterationBucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 02 | 80 | 45 3 | 75 | 04 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------After third iteration and so on......BucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 02 | 80 | 803 | 75 | 354 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------[/quote]sorry...but at the moment i am at a loss as to why you wish to store each iteration....can you please explain the business requriement behind this?Tue, 13 Sep 2016 10:47:44 GMTJ Livingston SQLRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxIt seems this is not what I am after. I want the filler data to feed to bucket in recursive way and store each recursion. the output should look like:After 1st iterationBucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 852 | 80 | 5 3 | 75 | 04 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------After second iterationBucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 02 | 80 | 45 3 | 75 | 04 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------After third iteration and so on......BucketID | FullCapacity | CurrentAmount ---+--------------+--------------1 | 85 | 02 | 80 | 803 | 75 | 354 | 70 | 05 | 50 | 06 | 40 | 0---+--------------+--------------Tue, 13 Sep 2016 10:35:05 GMTashishkumarraiRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]ashishkumarrai (9/13/2016)[/b][hr][quote][b]ChrisM@Work (9/13/2016)[/b][hr][quote][b]ashishkumarrai (9/13/2016)[/b][hr][quote][b]ChrisM@Work (9/9/2016)[/b][hr]This should be a step in the right direction:[code="sql"]DROP TABLE #BucketsCREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);INSERT INTO #BucketsVALUES ( '1', 85, 0 ) , ( '2', 80, 0 ) , ( '3', 75, 0 ) , ( '4', 70, 0 ) , ( '5', 50, 0 ) , ( '6', 40, 0 ); DROP TABLE #FillerCREATE TABLE #Filler (FillerID INT, Filler INT); INSERT INTO #FillerVALUES ( '1', 90 ) , ( '2', 40 ) , ( '3', 70 ) , ( '4', 50 ) , ( '5', 40 ) , ( '6', 30 ) , ( '7', 35 );WITH ProcessedDebits AS ( SELECT bucketID, FullCapacity, [from] = ([to] - FullCapacity), [to] FROM (SELECT *, [to] = SUM(FullCapacity) OVER (PARTITION BY 1 ORDER BY bucketID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Buckets) d),ProcessedCredits AS ( SELECT FillerID, Filler, [from] = ([to] - Filler), [to] FROM (SELECT *, [to] = SUM(Filler) OVER (PARTITION BY 1 ORDER BY FillerID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Filler) d)SELECT bucketID, FullCapacity, DebitBalance = CASE WHEN dr.[to] >= cr.[to] THEN (dr.[to] - cr.[to]) WHEN dr.[to] < cr.[to] THEN 0 ELSE dr.[to] - MAX(cr.[to]) OVER(PARTITION BY 1 ORDER BY dr.bucketID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END, FillerID, Filler, CreditBalance = CASE WHEN cr.[to] >= dr.[to] THEN (cr.[to] - dr.[to]) WHEN cr.[to] < dr.[to] THEN 0 ELSE cr.[to] - MAX(dr.[to]) OVER(PARTITION BY 1 ORDER BY cr.FillerID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ENDFROM ProcessedDebits drFULL OUTER JOIN ProcessedCredits cr ON cr.[from] < dr.[to] AND cr.[to] > dr.[from] ORDER BY bucketID, FillerIDOPTION (MAXDOP 1);[/code][/quote]I think this will not run on SQL 2008, Can we achieve this in 2008?[/quote]SUM() OVER... works in 2008 ([url=https://msdn.microsoft.com/en-us/library/ms187810.aspx]https://msdn.microsoft.com/en-us/library/ms187810.aspx[/url]), however the window frame is I think from 2012. Try removing them:ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWthen run the test batch.[/quote]still not working the error is "The Parallel Data Warehouse (PDW) features are not enabled."[/quote]OK got it - running totals using [i]ORDER BY [/i]with SUM() OVER was introduced with SQL 2012. The running totals will have to be calculated by an alternative method.Tue, 13 Sep 2016 03:15:27 GMTChrisM@WorkRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]ChrisM@Work (9/13/2016)[/b][hr][quote][b]ashishkumarrai (9/13/2016)[/b][hr][quote][b]ChrisM@Work (9/9/2016)[/b][hr]This should be a step in the right direction:[code="sql"]DROP TABLE #BucketsCREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);INSERT INTO #BucketsVALUES ( '1', 85, 0 ) , ( '2', 80, 0 ) , ( '3', 75, 0 ) , ( '4', 70, 0 ) , ( '5', 50, 0 ) , ( '6', 40, 0 ); DROP TABLE #FillerCREATE TABLE #Filler (FillerID INT, Filler INT); INSERT INTO #FillerVALUES ( '1', 90 ) , ( '2', 40 ) , ( '3', 70 ) , ( '4', 50 ) , ( '5', 40 ) , ( '6', 30 ) , ( '7', 35 );WITH ProcessedDebits AS ( SELECT bucketID, FullCapacity, [from] = ([to] - FullCapacity), [to] FROM (SELECT *, [to] = SUM(FullCapacity) OVER (PARTITION BY 1 ORDER BY bucketID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Buckets) d),ProcessedCredits AS ( SELECT FillerID, Filler, [from] = ([to] - Filler), [to] FROM (SELECT *, [to] = SUM(Filler) OVER (PARTITION BY 1 ORDER BY FillerID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Filler) d)SELECT bucketID, FullCapacity, DebitBalance = CASE WHEN dr.[to] >= cr.[to] THEN (dr.[to] - cr.[to]) WHEN dr.[to] < cr.[to] THEN 0 ELSE dr.[to] - MAX(cr.[to]) OVER(PARTITION BY 1 ORDER BY dr.bucketID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END, FillerID, Filler, CreditBalance = CASE WHEN cr.[to] >= dr.[to] THEN (cr.[to] - dr.[to]) WHEN cr.[to] < dr.[to] THEN 0 ELSE cr.[to] - MAX(dr.[to]) OVER(PARTITION BY 1 ORDER BY cr.FillerID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ENDFROM ProcessedDebits drFULL OUTER JOIN ProcessedCredits cr ON cr.[from] < dr.[to] AND cr.[to] > dr.[from] ORDER BY bucketID, FillerIDOPTION (MAXDOP 1);[/code][/quote]I think this will not run on SQL 2008, Can we achieve this in 2008?[/quote]SUM() OVER... works in 2008 ([url=https://msdn.microsoft.com/en-us/library/ms187810.aspx]https://msdn.microsoft.com/en-us/library/ms187810.aspx[/url]), however the window frame is I think from 2012. Try removing them:ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWthen run the test batch.[/quote]still not working the error is "The Parallel Data Warehouse (PDW) features are not enabled."Tue, 13 Sep 2016 02:58:31 GMTashishkumarraiRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]ashishkumarrai (9/13/2016)[/b][hr][quote][b]ChrisM@Work (9/9/2016)[/b][hr]This should be a step in the right direction:[code="sql"]DROP TABLE #BucketsCREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);INSERT INTO #BucketsVALUES ( '1', 85, 0 ) , ( '2', 80, 0 ) , ( '3', 75, 0 ) , ( '4', 70, 0 ) , ( '5', 50, 0 ) , ( '6', 40, 0 ); DROP TABLE #FillerCREATE TABLE #Filler (FillerID INT, Filler INT); INSERT INTO #FillerVALUES ( '1', 90 ) , ( '2', 40 ) , ( '3', 70 ) , ( '4', 50 ) , ( '5', 40 ) , ( '6', 30 ) , ( '7', 35 );WITH ProcessedDebits AS ( SELECT bucketID, FullCapacity, [from] = ([to] - FullCapacity), [to] FROM (SELECT *, [to] = SUM(FullCapacity) OVER (PARTITION BY 1 ORDER BY bucketID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Buckets) d),ProcessedCredits AS ( SELECT FillerID, Filler, [from] = ([to] - Filler), [to] FROM (SELECT *, [to] = SUM(Filler) OVER (PARTITION BY 1 ORDER BY FillerID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Filler) d)SELECT bucketID, FullCapacity, DebitBalance = CASE WHEN dr.[to] >= cr.[to] THEN (dr.[to] - cr.[to]) WHEN dr.[to] < cr.[to] THEN 0 ELSE dr.[to] - MAX(cr.[to]) OVER(PARTITION BY 1 ORDER BY dr.bucketID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END, FillerID, Filler, CreditBalance = CASE WHEN cr.[to] >= dr.[to] THEN (cr.[to] - dr.[to]) WHEN cr.[to] < dr.[to] THEN 0 ELSE cr.[to] - MAX(dr.[to]) OVER(PARTITION BY 1 ORDER BY cr.FillerID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ENDFROM ProcessedDebits drFULL OUTER JOIN ProcessedCredits cr ON cr.[from] < dr.[to] AND cr.[to] > dr.[from] ORDER BY bucketID, FillerIDOPTION (MAXDOP 1);[/code][/quote]I think this will not run on SQL 2008, Can we achieve this in 2008?[/quote]SUM() OVER... works in 2008 ([url=https://msdn.microsoft.com/en-us/library/ms187810.aspx]https://msdn.microsoft.com/en-us/library/ms187810.aspx[/url]), however the window frame is I think from 2012. Try removing them:ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWthen run the test batch.Tue, 13 Sep 2016 02:48:05 GMTChrisM@WorkRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]ChrisM@Work (9/9/2016)[/b][hr]This should be a step in the right direction:[code="sql"]DROP TABLE #BucketsCREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);INSERT INTO #BucketsVALUES ( '1', 85, 0 ) , ( '2', 80, 0 ) , ( '3', 75, 0 ) , ( '4', 70, 0 ) , ( '5', 50, 0 ) , ( '6', 40, 0 ); DROP TABLE #FillerCREATE TABLE #Filler (FillerID INT, Filler INT); INSERT INTO #FillerVALUES ( '1', 90 ) , ( '2', 40 ) , ( '3', 70 ) , ( '4', 50 ) , ( '5', 40 ) , ( '6', 30 ) , ( '7', 35 );WITH ProcessedDebits AS ( SELECT bucketID, FullCapacity, [from] = ([to] - FullCapacity), [to] FROM (SELECT *, [to] = SUM(FullCapacity) OVER (PARTITION BY 1 ORDER BY bucketID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Buckets) d),ProcessedCredits AS ( SELECT FillerID, Filler, [from] = ([to] - Filler), [to] FROM (SELECT *, [to] = SUM(Filler) OVER (PARTITION BY 1 ORDER BY FillerID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Filler) d)SELECT bucketID, FullCapacity, DebitBalance = CASE WHEN dr.[to] >= cr.[to] THEN (dr.[to] - cr.[to]) WHEN dr.[to] < cr.[to] THEN 0 ELSE dr.[to] - MAX(cr.[to]) OVER(PARTITION BY 1 ORDER BY dr.bucketID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END, FillerID, Filler, CreditBalance = CASE WHEN cr.[to] >= dr.[to] THEN (cr.[to] - dr.[to]) WHEN cr.[to] < dr.[to] THEN 0 ELSE cr.[to] - MAX(dr.[to]) OVER(PARTITION BY 1 ORDER BY cr.FillerID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ENDFROM ProcessedDebits drFULL OUTER JOIN ProcessedCredits cr ON cr.[from] < dr.[to] AND cr.[to] > dr.[from] ORDER BY bucketID, FillerIDOPTION (MAXDOP 1);[/code][/quote]I think this will not run on SQL 2008, Can we achieve this in 2008?Tue, 13 Sep 2016 02:40:25 GMTashishkumarraiRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxYes, I would like to show all iteration, recursion as well. Final recursion/iteration should look like what you shown.Tue, 13 Sep 2016 02:37:45 GMTashishkumarraiRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxI think this will not run on SQL 2008, Can we achieve this in 2008?Tue, 13 Sep 2016 02:36:19 GMTashishkumarraiRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]CELKO (9/10/2016)[/b][hr]CREATE TABLE Buckets(bucket_nbr INTEGER NOT NULL PRIMARY KEY, bucket_size INTEGER NOT NULL CHECK (bucket_size > 0, bucket_content INTEGER NOT NULL CHECK (bucket_content BETWEEN 0 AND bucket_size)); INSERT INTO Buckets (bucket_size, bucket_content, bucket_nbr) (1, 10, 0), (2, 5, 0), (3, 10, 0), (4, 10, 0);There are actually several different ways of doing this. Using a "greedy algorithm", we fill the biggest buckets first. But you could just as easily start filling the smallest buckets. In general there is no perfect way of doing it. Google "martello toth bin packing" and look at all the free PDF files which you can download on the topic. Try playing with this query:SELECT bucket_nbr, bucket_size, bucket_content, SUM(bucket_content) OVER (ORDER BY bucket_size DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURENT ROW) AS greedy_running_total, SUM(bucket_size) ROWS BETWEEN UNBOUNDED PRECEDING AND CURENT ROW) AS capacity_running_total FROM Buckets;(1, 10, 10, NULL)(3, 10, 20, NULL)(4, 10, 30, NULL)(2, 5, 35, NULL)Without going into the code, you can see that your 21 liters will overflow bucket #1, then bucket #2, but not bucket #4. We never get to bucket #2, because we ran out of water. This means we need some logic (hint: it can be done with case expressions in the UPDATE statement) to classify each of the buckets as {'full', 'empty', 'partial'} (1, 10, 10, 10) -- full(3, 10, 20, 10) -- full(4, 10, 30, 1) -- partial, and needs math (2, 5, 35, 0) -- empty = 0In the body the procedure, it is probably a good idea to have a test for (SUM(bucket_size) >= @input_amount) so that you know your task is impossible due to lack of capacity.[/quote]bit confused on this....there are two tables....(Buckets/Filler)...which you only include "Buckets"your code that you provide doesnt parse and throws errors...?can you please review and advise....will be appreciatededit>>>for clarification I refer to the latest question on this post....probably better if it was a new thread[b][url]http://www.sqlservercentral.com/Forums/FindPost1816384.aspx[/url][/b][quote]Posted my problem with all the details and sample data over here. [url]https://ask.sqlservercentral.com/questions/140089/sql-server-2008-cte-bucket-filling.html[/url][/quote]Sat, 10 Sep 2016 14:23:51 GMTJ Livingston SQLRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxCREATE TABLE Buckets(bucket_nbr INTEGER NOT NULL PRIMARY KEY, bucket_size INTEGER NOT NULL CHECK (bucket_size > 0, bucket_content INTEGER NOT NULL CHECK (bucket_content BETWEEN 0 AND bucket_size)); INSERT INTO Buckets (bucket_size, bucket_content, bucket_nbr) (1, 10, 0), (2, 5, 0), (3, 10, 0), (4, 10, 0);There are actually several different ways of doing this. Using a "greedy algorithm", we fill the biggest buckets first. But you could just as easily start filling the smallest buckets. In general there is no perfect way of doing it. Google "martello toth bin packing" and look at all the free PDF files which you can download on the topic. Try playing with this query:SELECT bucket_nbr, bucket_size, bucket_content, SUM(bucket_content) OVER (ORDER BY bucket_size DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURENT ROW) AS greedy_running_total, SUM(bucket_size) ROWS BETWEEN UNBOUNDED PRECEDING AND CURENT ROW) AS capacity_running_total FROM Buckets;(1, 10, 10, NULL)(3, 10, 20, NULL)(4, 10, 30, NULL)(2, 5, 35, NULL)Without going into the code, you can see that your 21 liters will overflow bucket #1, then bucket #2, but not bucket #4. We never get to bucket #2, because we ran out of water. This means we need some logic (hint: it can be done with case expressions in the UPDATE statement) to classify each of the buckets as {'full', 'empty', 'partial'} (1, 10, 10, 10) -- full(3, 10, 20, 10) -- full(4, 10, 30, 1) -- partial, and needs math (2, 5, 35, 0) -- empty = 0In the body the procedure, it is probably a good idea to have a test for (SUM(bucket_size) >= @input_amount) so that you know your task is impossible due to lack of capacity.Sat, 10 Sep 2016 13:49:57 GMTCELKORE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]ashishkumarrai (9/9/2016)[/b][hr]Posted my problem with all the details and sample data over here. [url=https://ask.sqlservercentral.com/questions/140089/sql-server-2008-cte-bucket-filling.html]https://ask.sqlservercentral.com/questions/140089/sql-server-2008-cte-bucket-filling.html[/url][/quote]can you please provide your expected results from the sample data?edit >>>is this the result you are looking for?[code="plain"]╔══════════╦══════════════╦═══════════════════╗║ BucketId ║ Fullcapacity ║ RemainingCapacity ║╠══════════╬══════════════╬═══════════════════╣║ 1 ║ 85 ║ 0 ║╠══════════╬══════════════╬═══════════════════╣║ 2 ║ 80 ║ 0 ║╠══════════╬══════════════╬═══════════════════╣║ 3 ║ 75 ║ 0 ║╠══════════╬══════════════╬═══════════════════╣║ 4 ║ 70 ║ 0 ║╠══════════╬══════════════╬═══════════════════╣║ 5 ║ 50 ║ 5 ║╠══════════╬══════════════╬═══════════════════╣║ 6 ║ 40 ║ 40 ║╚══════════╩══════════════╩═══════════════════╝[/code]Sat, 10 Sep 2016 07:08:24 GMTJ Livingston SQLRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxThis should be a step in the right direction:[code="sql"]DROP TABLE #BucketsCREATE TABLE #Buckets (bucketID INT, FullCapacity INT, CurrentAmount INT);INSERT INTO #BucketsVALUES ( '1', 85, 0 ) , ( '2', 80, 0 ) , ( '3', 75, 0 ) , ( '4', 70, 0 ) , ( '5', 50, 0 ) , ( '6', 40, 0 ); DROP TABLE #FillerCREATE TABLE #Filler (FillerID INT, Filler INT); INSERT INTO #FillerVALUES ( '1', 90 ) , ( '2', 40 ) , ( '3', 70 ) , ( '4', 50 ) , ( '5', 40 ) , ( '6', 30 ) , ( '7', 35 );WITH ProcessedDebits AS ( SELECT bucketID, FullCapacity, [from] = ([to] - FullCapacity), [to] FROM (SELECT *, [to] = SUM(FullCapacity) OVER (PARTITION BY 1 ORDER BY bucketID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Buckets) d),ProcessedCredits AS ( SELECT FillerID, Filler, [from] = ([to] - Filler), [to] FROM (SELECT *, [to] = SUM(Filler) OVER (PARTITION BY 1 ORDER BY FillerID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM #Filler) d)SELECT bucketID, FullCapacity, DebitBalance = CASE WHEN dr.[to] >= cr.[to] THEN (dr.[to] - cr.[to]) WHEN dr.[to] < cr.[to] THEN 0 ELSE dr.[to] - MAX(cr.[to]) OVER(PARTITION BY 1 ORDER BY dr.bucketID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) END, FillerID, Filler, CreditBalance = CASE WHEN cr.[to] >= dr.[to] THEN (cr.[to] - dr.[to]) WHEN cr.[to] < dr.[to] THEN 0 ELSE cr.[to] - MAX(dr.[to]) OVER(PARTITION BY 1 ORDER BY cr.FillerID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ENDFROM ProcessedDebits drFULL OUTER JOIN ProcessedCredits cr ON cr.[from] < dr.[to] AND cr.[to] > dr.[from] ORDER BY bucketID, FillerIDOPTION (MAXDOP 1);[/code]Fri, 09 Sep 2016 07:46:31 GMTChrisM@WorkRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxPosted my problem with all the details and sample data over here. [url=https://ask.sqlservercentral.com/questions/140089/sql-server-2008-cte-bucket-filling.html]https://ask.sqlservercentral.com/questions/140089/sql-server-2008-cte-bucket-filling.html[/url]Fri, 09 Sep 2016 05:20:40 GMTashishkumarraiRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]ashishkumarrai (9/6/2016)[/b][hr]Hi Thanks for the post, really useful. I am working on similar stuff. My problem is the value the above query is using is static. What will be the solution if the value is also a table. If the Variable above @AmountToAllocate is a table of values to be filled in this bucket. Any help is appreciated. I want to show all the records recursively till the bucket gets filled.[/quote]You've come along at a good time, quite a few folks have recently worked on similar problems. Before doing anything else, have a quick scan through [url=http://www.sqlservercentral.com/articles/Best+Practices/61537/]this article[/url]. We'll need sample data scripts and a script to generate your expected result set from the sample data. The article shows you how to do this.Also, start a new thread - your scenario is different to the one covered by this thread.CheersTue, 06 Sep 2016 10:15:03 GMTChrisM@WorkRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxHi Thanks for the post, really useful. I am working on similar stuff. My problem is the value the above query is using is static. What will be the solution if the value is also a table. If the Variable above @AmountToAllocate is a table of values to be filled in this bucket. Any help is appreciated. I want to show all the records recursively till the bucket gets filled.Tue, 06 Sep 2016 10:07:35 GMTashishkumarraiRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]dwain.c (9/11/2012)[/b][hr][quote][b]ChrisM@Work (9/11/2012)[/b][hr]Using the sample data provided, a simple calculation rCTE works fine with both positive and negative numbers.[code="sql"]DECLARE @AmountToAllocate INT = 21 ;WITH Calculator AS (SELECT BucketID, TotalSize, Amount, AmountLeftToAllocate = CASE WHEN @AmountToAllocate > (TotalSize - Amount) THEN @AmountToAllocate - (TotalSize - Amount) WHEN @AmountToAllocate < 0 AND ABS(@AmountToAllocate) > Amount THEN Amount + @AmountToAllocate ELSE 0 END, NewAmount = CASE WHEN @AmountToAllocate > (TotalSize - Amount) THEN TotalSize WHEN @AmountToAllocate < 0 AND ABS(@AmountToAllocate) > Amount THEN 0 ELSE Amount + @AmountToAllocate END FROM dbo.BucketsWHERE BucketID = 1UNION ALLSELECT tr.BucketID, tr.TotalSize, tr.Amount, AmountLeftToAllocate = CASE WHEN lr.AmountLeftToAllocate > (tr.TotalSize - tr.Amount) THEN lr.AmountLeftToAllocate - (tr.TotalSize - tr.Amount) WHEN lr.AmountLeftToAllocate < 0 AND ABS(lr.AmountLeftToAllocate) > tr.Amount THEN tr.Amount + lr.AmountLeftToAllocate ELSE 0 END, NewAmount = CASE WHEN lr.AmountLeftToAllocate > (tr.TotalSize - tr.Amount) THEN tr.TotalSize WHEN lr.AmountLeftToAllocate < 0 AND ABS(lr.AmountLeftToAllocate) > tr.Amount THEN 0 ELSE tr.Amount + lr.AmountLeftToAllocate END FROM dbo.Buckets trINNER JOIN Calculator lr ON lr.BucketID + 1 = tr.BucketID )SELECT BucketID, TotalSize, Amount = NewAmount, OldAmount = Amount FROM Calculator[/code][/quote]Nice one Chris! For some reason I just couldn't wrap my head around solving it that way.[/quote]Cheers buddy. It took two goes, the first was rubbish :-DThu, 13 Sep 2012 02:41:14 GMTChrisM@WorkRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspx[quote][b]ChrisM@Work (9/11/2012)[/b][hr]Using the sample data provided, a simple calculation rCTE works fine with both positive and negative numbers.[code="sql"]DECLARE @AmountToAllocate INT = 21 ;WITH Calculator AS (SELECT BucketID, TotalSize, Amount, AmountLeftToAllocate = CASE WHEN @AmountToAllocate > (TotalSize - Amount) THEN @AmountToAllocate - (TotalSize - Amount) WHEN @AmountToAllocate < 0 AND ABS(@AmountToAllocate) > Amount THEN Amount + @AmountToAllocate ELSE 0 END, NewAmount = CASE WHEN @AmountToAllocate > (TotalSize - Amount) THEN TotalSize WHEN @AmountToAllocate < 0 AND ABS(@AmountToAllocate) > Amount THEN 0 ELSE Amount + @AmountToAllocate END FROM dbo.BucketsWHERE BucketID = 1UNION ALLSELECT tr.BucketID, tr.TotalSize, tr.Amount, AmountLeftToAllocate = CASE WHEN lr.AmountLeftToAllocate > (tr.TotalSize - tr.Amount) THEN lr.AmountLeftToAllocate - (tr.TotalSize - tr.Amount) WHEN lr.AmountLeftToAllocate < 0 AND ABS(lr.AmountLeftToAllocate) > tr.Amount THEN tr.Amount + lr.AmountLeftToAllocate ELSE 0 END, NewAmount = CASE WHEN lr.AmountLeftToAllocate > (tr.TotalSize - tr.Amount) THEN tr.TotalSize WHEN lr.AmountLeftToAllocate < 0 AND ABS(lr.AmountLeftToAllocate) > tr.Amount THEN 0 ELSE tr.Amount + lr.AmountLeftToAllocate END FROM dbo.Buckets trINNER JOIN Calculator lr ON lr.BucketID + 1 = tr.BucketID )SELECT BucketID, TotalSize, Amount = NewAmount, OldAmount = Amount FROM Calculator[/code][/quote]Nice one Chris! For some reason I just couldn't wrap my head around solving it that way.Tue, 11 Sep 2012 18:10:26 GMTdwain.cRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxUsing the sample data provided, a simple calculation rCTE works fine with both positive and negative numbers.[code="sql"]DECLARE @AmountToAllocate INT = 21 ;WITH Calculator AS (SELECT BucketID, TotalSize, Amount, AmountLeftToAllocate = CASE WHEN @AmountToAllocate > (TotalSize - Amount) THEN @AmountToAllocate - (TotalSize - Amount) WHEN @AmountToAllocate < 0 AND ABS(@AmountToAllocate) > Amount THEN Amount + @AmountToAllocate ELSE 0 END, NewAmount = CASE WHEN @AmountToAllocate > (TotalSize - Amount) THEN TotalSize WHEN @AmountToAllocate < 0 AND ABS(@AmountToAllocate) > Amount THEN 0 ELSE Amount + @AmountToAllocate END FROM dbo.BucketsWHERE BucketID = 1UNION ALLSELECT tr.BucketID, tr.TotalSize, tr.Amount, AmountLeftToAllocate = CASE WHEN lr.AmountLeftToAllocate > (tr.TotalSize - tr.Amount) THEN lr.AmountLeftToAllocate - (tr.TotalSize - tr.Amount) WHEN lr.AmountLeftToAllocate < 0 AND ABS(lr.AmountLeftToAllocate) > tr.Amount THEN tr.Amount + lr.AmountLeftToAllocate ELSE 0 END, NewAmount = CASE WHEN lr.AmountLeftToAllocate > (tr.TotalSize - tr.Amount) THEN tr.TotalSize WHEN lr.AmountLeftToAllocate < 0 AND ABS(lr.AmountLeftToAllocate) > tr.Amount THEN 0 ELSE tr.Amount + lr.AmountLeftToAllocate END FROM dbo.Buckets trINNER JOIN Calculator lr ON lr.BucketID + 1 = tr.BucketID )SELECT BucketID, TotalSize, Amount = NewAmount, OldAmount = Amount FROM Calculator[/code]Tue, 11 Sep 2012 07:24:42 GMTChrisM@WorkRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxThanks for the links guys. Obviously wasn't looking hard enough.:-)Tue, 11 Sep 2012 05:08:20 GMTfaiseljRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxThe first looks more like a bin packing problem to me:[url]http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/30/bin-packing-part-1-setting-a-baseline.aspx[/url]There's a series of 5 articles by Hugo Kornelis at this link (to the first). Very complicated, but the fastest solutions typically involve a set-based loop of some sort.You didn't mention if speed is an issue for you. The CURSOR will work OK as long as you don't have too many buckets to fill.PM me if you would like more information.Tue, 11 Sep 2012 04:03:41 GMTdwain.cRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxI think this is a running totals problem, have a look here[url]http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]If you are using SQL Server 2012, you can use the built-in windowing functions[code="sql"]DECLARE @ToAllocate INT = 21;WITH CTE AS (SELECT TotalSize, Amount, BucketID, TotalSize - Amount AS Remaining, SUM(TotalSize - Amount) OVER (ORDER BY BucketID ROWS UNBOUNDED PRECEDING) AS Remaining_RunningTotalFROM dbo.Buckets)SELECT TotalSize, Amount, BucketID, CASE WHEN Remaining_RunningTotal <= @ToAllocate THEN Remaining ELSE @ToAllocate - Remaining_RunningTotal + Remaining END AS AmountToAddFROM CTEWHERE Remaining_RunningTotal - Remaining < @ToAllocateORDER BY BucketID;[/code]Tue, 11 Sep 2012 03:53:35 GMTMark CowneRE: Filling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxInteresting. I have been working on a similar task. This is to take away sales figures (already sold items) from a set of monthly sales forecast figures.eg. Sales Forecast for an item:Month 1: 200, Sales 450 (already sold items in Month 1)Month 2: 100 (no Sales beyond current month, only future orders)Month 3: 100Month 4: 120I have to remove 450 from the month buckets, starting at M1.So the update forecast would be:M1: 0M2: 0M3: 0M4: 70There is an added constraint, which is to only only make adjustments up to a certain number of months in the future. eg. If Months to Consider = 4 then the result would be as above.But if Months to Consider = 3 then the result for Month 4 would remain at the original 120. The extra 70 from the Sales would become part of the original Sales forecast.Orders (not shown) as opposed to Sales also affect the forecast.Got no code to show, but it is similar to what you have shown.But I ended up using a cursor around the procedure because I have 6,000 item forecasts to process. (I couldn't work out how to do the sub-selects without the cursor, and was running out of time).I have not found anything much better than what you have shown. If I had some more time I would investigate, as I can't help feeling there may be some "Tally table" solution to this.Tue, 11 Sep 2012 03:17:18 GMTfaiseljFilling Bucketshttp://www.sqlservercentral.com/Forums/Topic568498-338-1.aspxA customer had reported an issue with one of our stored procedures so I took a look and found that the developer had used a cursor to implement his solution. Taking the “all cursors are evil” view I thought that I had better rewrite it, however it wasn’t as easy as I hoped.The problem can be simplified to... You have a set of buckets, with varying sizes, so of which are already full/partly full. You are then given some more water which you are to use to fill the buckets on a first-come-first-served basis.This can be modelled with the following table[code]create table dbo.Buckets ( TotalSize int not null, Amount int not null, BucketID int not null,constraint pk_Buckets primary key (BucketID),constraint ck_Buckets_Amount check ( Amount between 0 and TotalSize))go[/code]WhereTotalSize = the total amount the bucket can holdAmount = the amount currently in the bucketBucketID = unique id for the bucket, and is used to determine the order of the buckets ExampleSo if, we had the following 4 buckets[code]insert into dbo.Buckets (TotalSize,Amount,BucketID)select 10, 1, 1goinsert into dbo.Buckets (TotalSize,Amount,BucketID)select 5, 4, 2goinsert into dbo.Buckets (TotalSize,Amount,BucketID)select 10, 0, 3goinsert into dbo.Buckets (TotalSize,Amount,BucketID)select 10, 0, 4go[/code]and we had to allocate 21 units of water we would end up withBucket 1=10Bucket 2 =5Bucket 3 =10Bucket 4 =1[b]My Solution[/b]The solution I came up was to use two update statements, the first one to handle the buckets which would be completely filled, and the second one to partially fill the final bucket. This seemed to be working well, until I went to look at the issue reported by the customer - they also needed the ability to empty the buckets as well. My best solution so far (below), is to use another two update statements with an “if” statement to control which are to be used.So my questions are1. Is this a “standard” problem with a well known solution?2. Is there a better solution, as I have to use an ‘if’ statement and double-subselects.3. Is updating the @AmountToAllocate variable in an update statement a good idea?thanks in advanceDavidMy sql is...[code]-- The amount of water was have to allocatedeclare @AmountToAllocate intset @AmountToAllocate = 21-- 'Before'select * from dbo.Buckets-- If the amount is positive then we are filling the bucketsif @AmountToAllocate > 0 begin -- Fill these buckets completely, decrease our "amount to allocate" as we go. -- We update just the buckets then we can completely full. If we filled the following bucket then -- we would have exceed the amount of water we have been given to allocate. update dbo.Buckets set Amount = TotalSize, @AmountToAllocate = @AmountToAllocate - (TotalSize - Amount) where Amount != TotalSize and BucketID <= ( select max(B2.BucketID) from dbo.Buckets B2 where @AmountToAllocate >= ( select sum(TotalSize - Amount) from dbo.Buckets B3 where B3.BucketID <= B2.BucketID ) ) -- Part fill the remaining bucket update dbo.Buckets set Amount = Amount + @AmountToAllocate where BucketID = ( select min(B.BucketID) from dbo.Buckets B where B.Amount != B.TotalSize)endelsebegin--We have a negative amount so we are emptying the buckets -- Complete empty buckets update dbo.Buckets set Amount = 0, @AmountToAllocate = @AmountToAllocate + Amount where Amount != 0 and BucketID >= ( select min(B2.BucketID) from dbo.Buckets B2 where abs(@AmountToAllocate) >= ( select sum(Amount) from dbo.Buckets B3 where B3.BucketID >= B2.BucketID ) ) -- Part empty the remaining bucket update dbo.Buckets set Amount = Amount - abs(@AmountToAllocate) where BucketID = ( select max(B.BucketID) from dbo.Buckets B where B.Amount != 0)end--'After'select * from dbo.Buckets[/code]Fri, 12 Sep 2008 07:42:56 GMTDavid Betteridge