I have a table of users that are owned by various Owners. One person left and the list of users needs to be split up between two owners. How would you use SQL to randomly assign the users between two ownerIds . For example, a function that theoretically behaves like below
UPDATE Users
set OwnerId = RAND(64,72)
Where OwnerId = 37
We are utilizing SQL Server 2012. Tried looking at a random function between two users but is unable to find anything specific. Thank you,
You can use CHECKSUM(NEWID()) which returns an integer that is either odd or even, so then just check mod 2 (% 2).
UPDATE dbo.Users
SET OwnerId = CASE
WHEN CHECKSUM(NEWID()) % 2 = 0 THEN 64 ELSE 72 END
WHERE OwnerId = 37;
Working example in this fiddle.
If you want to be cryptic/clever or play code golf, then something like this can work in this very specific example:
UPDATE dbo.Users
SET OwnerId = 64 + (CHECKSUM(NEWID()) % 2 * 8)
WHERE OwnerId = 37;
The reason this works better than RAND() is because RAND() is only evaluated once, whereas NEWID() is evaluated for every row.
Following Larnu's recommendation, though, you could also do this:
WITH cte AS
(
SELECT ID, OwnerId, rn = ROW_NUMBER() OVER (ORDER BY ID)
FROM dbo.Users
WHERE OwnerId = 37
)
UPDATE cte SET OwnerId = 64 + (rn % 2 * 8);
Working example in this fiddle. This will produce a more even (but perhaps too predictable) distribution, it just can't be exactly even if there are an odd number of existing matching rows.
First let's discuss how to provide example data. One of the better ways is to provide the SQL to define an object and then populate it with data. In your case something like this would use useful:
DECLARE @users TABLE (UserID INT, OwnerID INT)
INSERT INTO @users (UserID, OwnerID) VALUES
(1, 100),(2, 100),(3, 100),(4, 100),(5, 100),(6, 100),(7, 100),(8, 100),(9, 100),(10, 100)
Now we can use that to provide a demo. If you truly want to randomly assign between two users, it's a bit more complicated. To divide the users evenly between two new owners you could do something like this
SELECT *, CASE WHEN UserID % 2 = 0 THEN 101 ELSE 102 END AS NewOwner
FROM @users
All we're doing here is finding the modulo of the UserID to look for it being 0. If the userID can be evenly divided by 2 then Owner 101 gets the user, otherwise Owner 102 gets them.
UserID OwnerID NewOwner
------------------------
1 100 102
2 100 101
3 100 102
4 100 101
5 100 102
6 100 101
7 100 102
8 100 101
9 100 102
10 100 101
If you want to split between more users, increase the modulo and provide more WHEN options:
SELECT *, CASE WHEN UserID % 3 = 0 THEN 101
WHEN UserID % 3 = 1 THEN 102
ELSE 103 END AS NewOwner
FROM @users
UserID OwnerID NewOwner
------------------------
1 100 102
2 100 103
3 100 101
4 100 102
5 100 103
6 100 101
7 100 102
8 100 103
9 100 101
10 100 102
To make the split actually random we can leverage the RAND function. This isn't usually random for each row, but we can bully it a bit.
SELECT *, CASE WHEN (ROUND(((3 - 1 -1) * RAND(CAST(NEWID() AS VARBINARY)) + 1), 0) - 1) = 0 THEN 101 ELSE 102 END AS NewOwner
FROM @users
UserID OwnerID NewOwner
1 100 101
2 100 101
3 100 101
4 100 102
5 100 102
6 100 101
7 100 102
8 100 101
9 100 102
10 100 102
We can add additional values like this: (note the extra WHEN and random sequence range increased to 4)
SELECT *, CASE WHEN (ROUND(((4 - 1 -1) * RAND(CAST(NEWID() AS VARBINARY)) + 1), 0) - 1) = 0 THEN 101
WHEN (ROUND(((4 - 1 -1) * RAND(CAST(NEWID() AS VARBINARY)) + 1), 0) - 1) = 1 THEN 102
ELSE 103 END AS NewOwner
FROM @users
UserID OwnerID NewOwner
------------------------
1 100 102
2 100 103
3 100 101
4 100 101
5 100 101
6 100 103
7 100 102
8 100 103
9 100 103
10 100 103
case when rand() > 0.5 then 64 else 72 endmay work.ROW_NUMBER, which could be based onNEW_IDor even just their surname) and the assign the odd numbers to one person and evens to the other.