Need some help with a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need some help with a query

Author

Topic

rbhatia
Starting Member

2 Posts

Posted - 2012-07-26 : 17:29:06

I have to write a query that I can't figure out the algorithm to.

Assuming I have a CustomersByRegion table. The table has the number of customers by region.
REGION NUM_CUSTOMERS
=============================
A 20
B 10
C 21
D 33
E 5
F 10
G 35
H 15

I have a group of salesmen (4, in this example) whom I want to designate for each set of customers in each region. Each salesman must get assigned a certain number of customers but there is a maximum limit to the number of customers that a salesman can get assigned (50)
So using the example table above, these are the possible assignments :
Sales person 1 : Regions G And H (total customers = 50)
Sales person 2 : Regions D and B (total customers = 43)
Sales person 3 : Regions A, C and E (total customers = 46)
Sales person 4 : Region F (total customers = 10)

Here is the SQL to set up the tables :

/****************************************************/
declare @CustomersByRegion table ( Region varchar(100), NumCustomers int)
insert into @CustomersByRegion (Region,NumCustomers)
values
('A',20),
('B',10),
('C',21),
('D',33),
('E',5),
('F',10),
('G',35),
('H',15)

declare @TotalNumberOfCustomers int
SET @TotalNumberOfCustomers=(select sum(NumCustomers) from @CustomersByRegion)
declare @NumberOfSalesmen tinyint=4
Declare @BatchCount int
SET @BatchCount=ceiling((@TotalNumberOfCustomers*1.0)/@NumberOfSalesmen)
select TotalNumberOfCustomers=@TotalNumberOfCustomers,NumberOfSalesmen=@NumberOfSalesmen,BatchCount=@BatchCount

/****************************************************/
So based on the above query, I have 4 sales people. And each sales person should get assigned 50 customers each. So I need a query that will allow me to pick regions where the sum of the number of customers in each region does not exceed the maximum limit of 50.
I figured out how to do this using a cursor or a loop where for each sales person, I assign the regions and sum up the customers for each region and once the sum goes over the max, I go to the next sales person.
However, I want to know how I can do this using a set-based query.
I tried using a recursive query but I can't seem to figure out the query to make this work using a set based algorithm.
Was hoping someone on this forum could help.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-07-26 : 18:09:36

would help if you had adjoining table RegionCustomer(RegionID, CustomerID)

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources