Update Customer table from selected Ids and assign one of three distinct values to each-CodePudding

I have a fairly complex query which results in a list of Customer Ids where they have not ordered within a defined period, have no Rep assigned to them, and are business customers rather than private. This simply returns a sequential list of Ids which match the BIGINT primary keys of the matching customers, like this:

I now want to update the records with those Ids and assign to each a RepId from one of three Sales Reps:

RepName RepId
Fred    25
Doris   50
Sybil   99

My problem is to assign the rep Ids. Actually doing it is easy, as per this pseudocode:

UPDATE Customers SET RepId = [CalculatedRepId] WHERE CustomerId IN (QueryResult)

The problem is calculating the relevant Id. I have considered a number of approaches including selecting RowNumber() and using the number and its relation to three to select the number, and incrementing a variable and using that to assign the relevant Id, but I cannot get it to work. I also considered splitting the results into three and assigning separately but again cannot work out how to do it and in any case that is definitely not an ideal approach - for a start with imported ordered data that would skew the results giving each rep customers in a range depending on entry order.

The desired result would be something like this:

CustomerId RepId
13         25
17         50
51         99
76         25
87         50
97         99
123        25
131        50
etc.

The order of the RepIds is not necessarily critical, but shown as I originally envisaged it the idea being to share the customers between the Reps as evenly as possible.

EDIT - the final solution

I was able to apply the list of Ids by simply adding a WHERE clause to the cte_customers proposed by @forpas:

WITH 
  Reps AS (SELECT * FROM (VALUES (1), (3), (12)) v(RepId)), -- your RepIds
  cte_reps AS (SELECT RepId, ROW_NUMBER() OVER (ORDER BY RepId) - 1 rn, COUNT(*) OVER () cnt FROM Reps),
  cte_customers AS (SELECT *, ROW_NUMBER() OVER (ORDER BY CustomerId) - 1 rn FROM Customers WHERE Customers.CustomerId IN 
  (
        SELECT CustomerId FROM Customers WHERE Inactive = 0 AND TotalBlock = 0 AND RepId < 1 AND TypeId > 0 AND TypeId <> 10 
        AND NOT Exists (SELECT CustomerId FROM @TransIds t WHERE t.CustomerId = Customers.CustomerId))
  )
UPDATE c
SET c.RepId = r.RepId
FROM cte_customers c INNER JOIN cte_reps r
ON r.rn = c.rn % r.cnt;

That worked perfectly!!

CodePudding user response：

Assuming there is a table Reps with the RepIds that you want to use to update the table Customers, you can use ROW_NUMBER() window function to join the tables in the UPDATE statement:

WITH 
  cte_reps AS (SELECT RepId, ROW_NUMBER() OVER (ORDER BY RepId) - 1 rn, COUNT(*) OVER () cnt FROM Reps),
  cte_customers AS (SELECT *, ROW_NUMBER() OVER (ORDER BY CustomerId) - 1 rn FROM Customers)
UPDATE c
SET c.RepId = r.RepId
FROM cte_customers c INNER JOIN cte_reps r
ON r.rn = c.rn % r.cnt;

See the demo.

If you want to supply only specific RepIds use an additional CTE:

WITH 
  reps AS (SELECT * FROM (VALUES (25), (50), (99)) v(RepId)), -- your RepIds
  cte_reps AS (SELECT RepId, ROW_NUMBER() OVER (ORDER BY RepId) - 1 rn, COUNT(*) OVER () cnt FROM Reps),
  cte_customers AS (SELECT *, ROW_NUMBER() OVER (ORDER BY CustomerId) - 1 rn FROM Customers)
UPDATE c
SET c.RepId = r.RepId
FROM cte_customers c INNER JOIN cte_reps r
ON r.rn = c.rn % r.cnt;

See the demo.