SQL - How to evenly distribute clients between sellers-CodePudding

I have a list of clients and a list o sales people, i need to join those lists and distribute the clients evenly between sellers that are avaliable in the same city as them:

[ enter image description here

I did a join and some stuff and got a result like this:

But I need an end result like this:

CodePudding user response：

This could be possible if you define some rank on each client and and city table. Refer below code and output for the reference:


with CLIENT_TABLE as 
(SELECT *, CASE WHEN CLIENTS = 'Anne' then 1
                 when CLIENTS = 'Antony' then 2
                 when CLIENTS = 'Lucas' then 3
                 when CLIENTS = 'Jonh' then 4
                 when CLIENTS = 'Sabrine' then 1
                 when CLIENTS = 'Anastasia' then 2
                 when CLIENTS = 'Charles' then 5
                 when CLIENTS = 'Sual' then 6 else 9 end as  CLIENT_RANK
                  FROM(
  select 'Anne' as CLIENTS, 'NY' as CITY UNION ALL
  SELECT 'Antony', 'NY' UNION ALL
  SELECT 'Lucas', 'NY' UNION ALL
  SELECT 'Jonh', 'NY' UNION ALL
  SELECT 'Sabrine', 'NY' UNION ALL
  SELECT 'Anastasia', 'NY' UNION ALL
  SELECT 'Charles', 'DAKOTA' UNION ALL
  SELECT 'Sual', 'DAKOTA' )
),

CITY_TABLE AS 
(SELECT * , 
case when SELLER = 'Richard' then 1
     when SELLER = 'Ana' then 2
     when SELLER = 'Joseph' then 3
     when SELLER = 'Rikard' then 4
     when SELLER = 'Joanne' then 5
     when SELLER = 'Jane' then 6 else 7 end as  CITY_RANK
 FROM (
  SELECT 'NY' AS CITY , 'Richard' as SELLER UNION ALL
  SELECT 'NY','Ana' UNION ALL
  SELECT 'NY','Joseph' UNION ALL
  SELECT 'NY','Rikard' UNION ALL
  SELECT 'DAKOTA','Joanne' UNION ALL
  SELECT 'DAKOTA','Jane' 
       )
)

SELECT CLIENTS, B.CITY, SELLER, CLIENT_RANK,CITY_RANK  FROM CLIENT_TABLE A
left join  CITY_TABLE B ON A.CITY = B.CITY 
WHERE CLIENT_RANK = CITY_RANK ;

Result Given Below:

CodePudding user response：

Consider below query:

WITH numbered_sellers AS (
  SELECT *, ROW_NUMBER() OVER w - 1 AS num, COUNT(seller) OVER w total
    FROM sellers 
  WINDOW w AS (PARTITION BY city)
),
numbered_clients AS (
  SELECT *, ROW_NUMBER() OVER w num
    FROM clients 
  WINDOW w AS (PARTITION BY city)
)
SELECT c.*, s.seller
  FROM numbered_clients c JOIN numbered_sellers s ON c.city = s.city AND MOD(c.num, s.total) = s.num