This is a classic "Bin Stacking" problem. So far as I know, there's not a set based way to pull one of these off in T-SQL. You have to use some form of RBAR to do this.
As a bit of a sidebar, take a look at the first link in my signature line below under "Helpful Links". That'll tell you how to post "readily consumable data" to make it a bit easier on folks trying to help you. Not doing it that way is probably one of the big reasons why no one responded to your post any more quickly.
Here's another way to post such example data. It's what I used to test this and I'm posting it so if someone has a better idea on this problem, it's easier for them to test with.
One thing that you need to be made aware of is that I added a column to the Driver table. You should make such a change to your real table and make sure that both tables have an appropriate PK.
-- Setup and populate the test tables
--===== If the test tables already exist, drop them to make reruns easier in SSMS
IF OBJECT_ID('tempdb..#Route' ,'U') IS NOT NULL DROP TABLE #Route;
IF OBJECT_ID('tempdb..#Driver','U') IS NOT NULL DROP TABLE #Driver;
--===== Build the test tables with the data provided.
-- Note that a column has been added to the Drivers table.
SELECT 1 ,'Route01',700 ,0 UNION ALL
SELECT 2 ,'Route02',210 ,0 UNION ALL
SELECT 3 ,'Route03',111 ,0 UNION ALL
SELECT 4 ,'Route04',452 ,0 UNION ALL
SELECT 5 ,'Route05',236 ,0 UNION ALL
SELECT 6 ,'Route06',111 ,0 UNION ALL
SELECT 7 ,'Route07',300 ,0 UNION ALL
SELECT 8 ,'Route08',421 ,0 UNION ALL
SELECT 9 ,'Route09',1200,0 UNION ALL
SELECT 10,'Route10',525 ,0
) d (RouteID, RouteName, HouseHolds, DriverID)
SELECT 1,'Bob' ,0 UNION ALL
SELECT 2,'John' ,0 UNION ALL
SELECT 3,'Ricky Bobby' ,0 UNION ALL
SELECT 4,'Batman' ,0
) d (DriverID, Name, TotalHouseholds) --<<< Added the TotalHouseholds column
--===== Add the required PK's to the tables
ALTER TABLE #Route ADD PRIMARY KEY CLUSTERED (RouteID);
ALTER TABLE #Driver ADD PRIMARY KEY CLUSTERED (DriverID);
Here's a classic cursor solution for a classic RBAR-only problem. As always, details are in the comments in the code. Of course, you'll need to change the table names for your implementation.
-- Distribute the routes using a classic "Bin Fill".
-- The basic premise is to cycle through the routes in descending order according to the number of households in
-- the routes and assign the next value to the driver with the fewest number of households. That driver will
-- change on every iteration until we run out of routes.
-- What we end up with is the route assignments by driver in the route table and a total number of households
-- for each driver in the driver table.
--===== Environmental presets for performance of RBAR.
SET NOCOUNT ON;
--===== Reset the Route and Driver tables to make reruns possible.
UPDATE #Route SET DriverID = 0;
UPDATE #Driver SET TotalHouseholds = 0;
--===== Declare the necessary obviously named variables.
-- Because we're using a cursor instead of temp tables, we don't need any counters.
DECLARE @Households INT
--===== Define the cursor from the Route table.
-- Note that the number of households are in descending order.
DECLARE BinFill CURSOR LOCAL FORWARD_ONLY
FOR SELECT Households --This is all we need from the table. The cursor keeps track of everything else.
ORDER BY HouseHolds DESC, RouteID ASC --TieBreaker
FOR UPDATE OF DriverID --This is key to updating the table where the cursor is currently positioned "WHERE CURRENT OF"
--===== Open the cursor so that we can use it.
--===== Get the first row from the cursor
FETCH NEXT FROM BinFill
--===== Loop through the rows in the cursor
WHILE @@FETCH_STATUS = 0
--===== Identify the driver with the fewest households for this iteration.
-- The DriverID is the tie breaker just to be pedantic. ;-)
SELECT TOP 1 @DriverID = DriverID FROM #Driver ORDER BY TotalHouseholds ASC, DriverID ASC
--===== Add the # of Households value from the cursor row to the designated driver
SET d.TotalHouseholds = d.TotalHouseholds + @Households --@Households came from the cursor row,
FROM #Driver d
WHERE d.DriverID = @DriverID
--===== Update the Route table with the current DriverID according to the current position of the cursor.
-- It's freakin' magic. ;-)
SET DriverID = @DriverID
WHERE CURRENT OF BinFill
--===== Get the next row. If we're out of rows, the fetch status will change to -1 auto-magically
FETCH NEXT FROM BinFill
--======== Properly close the cursor
--===== All done. Let's see the results
SELECT * FROM #Driver;
SELECT * FROM #Route;
Last but not least, here are the results...
DriverID Name TotalHouseholds
----------- ----------- ---------------
1 Bob 1200
2 John 1047
3 Ricky Bobby 1035
4 Batman 984
(4 row(s) affected)
RouteID RouteName HouseHolds DriverID
----------- --------- ----------- -----------
1 Route01 700 2
2 Route02 210 3
3 Route03 111 4
4 Route04 452 4
5 Route05 236 2
6 Route06 111 2
7 Route07 300 3
8 Route08 421 4
9 Route09 1200 1
10 Route10 525 3
(10 row(s) affected)
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs