Allocate Unique Random case number SQL 2008

Question

I have a list of teams in one table and list of cases in another table. I have to allocate a unique random case number to each one of the members in the team. What is the best way to generate unique random case number for each team member. I have read about NewID() and CRYPT_GEN_RANDOM(4) functions. I tried using them but not getting unique number for each team member. Can some one please help me. Thanks for your time. I am using SQL 2008.

I have a 'Teams' table which has team members, their ids(TM1,TM2 etc.) and their names. I have another 'Cases' table which has ID numbers like 1,2,3,4 etc. I want to allocate random case to each team member. The desired output should be as below.

 Team member    Random_case_allocated
      TM1           3
      TM2           5
      TM3           7
      TM4           2
      TM5           8
      TM6           6

I have tried SELECT TOP 1 id FROM cases ORDER BY CRYPT_GEN_RANDOM(4)

It is giving the same id for all team members. I want a different case id for each team member. Can someone please help. Thank you.

Answer 1

The TOP(1) ORDER BY NEWID() will not work the way you are trying to get it to work here. The TOP is telling the query engine you are only interested on the first record of the result set. You need to have the NEWID() evaluate for each record. You can force this inside of a window function, such as ROW_NUMBER(). This could optimized I would imagine, however, it was what I could come up with from the top of my head. Please note, this is not nearly a truly random algorithm.

UPDATED With Previous Case Exclusions

DECLARE @User TABLE(UserId INT)
DECLARE @Case TABLE(CaseID INT)
DECLARE @UserCase TABLE (UserID INT, CaseID INT, DateAssigned DATETIME)

DECLARE @CaseCount INT =10
DECLARE @SaveCaseID INT = @CaseCount
DECLARE @UserCount INT = 100
DECLARE @NumberOfUserAllocatedAtStart INT= 85

WHILE(@CaseCount > 0)BEGIN
    INSERT @Case VALUES(@CaseCount) 
    SET @CaseCount = @CaseCount-1
END

DECLARE @RandomCaseID INT
WHILE(@UserCount > 0)BEGIN
    INSERT @User VALUES(@UserCount) 
    SET @UserCount = @UserCount-1

    IF(@NumberOfUserAllocatedAtStart > 0 )BEGIN
        SET @RandomCaseID = (ABS(CHECKSUM(NewId())) % (@SaveCaseID))+1
        INSERT @UserCase SELECT @UserCount,@RandomCaseID,DATEADD(MONTH,-3,GETDATE())

        SET @RandomCaseID = (ABS(CHECKSUM(NewId())) % (@SaveCaseID))+1
        INSERT @UserCase SELECT @UserCount,@RandomCaseID,DATEADD(MONTH,-5,GETDATE())

        SET @RandomCaseID = (ABS(CHECKSUM(NewId())) % (@SaveCaseID))+1
        INSERT @UserCase SELECT @UserCount,@RandomCaseID,DATEADD(MONTH,-2,GETDATE())

        SET @NumberOfUserAllocatedAtStart=@NumberOfUserAllocatedAtStart-1
    END
END

;WITH RowNumberWithNewID AS
(
    SELECT 
        U.UserID, C.CaseID, UserCase_CaseID = UC.CaseID,
        RowNumber = ROW_NUMBER() OVER (PARTITION BY U.UserID ORDER BY NEWID())
    FROM  
        @User U 
        INNER JOIN @Case C ON 1=1
        LEFT OUTER JOIN @UserCase UC ON  UC.UserID=U.UserID AND UC.CaseID=C.CaseID AND UC.DateAssigned > DATEADD(MONTH, -4, UC.DateAssigned)
    WHERE
        UC.CaseID IS NULL OR UC.CaseID <> C.CaseID
)

SELECT 
    UserID,
    CaseID,
    PreviousCases = STUFF((SELECT ', '+CONVERT(NVARCHAR(10), UC.CaseID) FROM @UserCase UC WHERE UC.UserID=RN.UserID FOR XML PATH('')),1,1,'')
FROM RowNumberWithNewID RN
WHERE
    RN.RowNumber=1