Sorry for the confusing title, I wasn't sure what to write there.
I have a table of a few hundred records. I need to assign each record of this table to a much smaller dynamic table of users, and the users should alternate as to what records they get assigned.
For example, if TableA is
Row_Number() Id 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 10 10
and TableB is
Row_Number() Id 1 1 2 2 3 3
I need an end result set that is
UserId RecordId 1 1 2 2 3 3 1 4 2 5 3 6 1 7 2 8 3 9 1 10
I've managed to do something a bit messily using the mod operator, but I was curious if this same query could be run without the temp table and the variable.
The temp table is used because TableA is actually a User Defined Function that converts a comma-delimited string to a table, and I need the Count of the objects from the UDF.
-- Converts a comma-delimited string into a table SELECT Num as [UserId], Row_Number() OVER (ORDER BY (SELECT 1)) as [RowNo] INTO #tmpTest FROM dbo.StringToNumSet('2,3,1', ',') DECLARE @test int SELECT @test = Count(*) FROM #tmpTest SELECT * FROM #tmpTest as T1 INNER JOIN ( SELECT Top 10 Id, Row_Number() OVER (ORDER BY SomeDateTime) as [RowNo] FROM TableA WITH (NOLOCK) ) as T2 ON T1.RowNo = (T2.RowNo % @test) + 1
Its important that the UserIds alternate too. I cannot assign the top 1/3 of the records to User1, second 1/3 of the records to User2, and 3rd 1/3 of the records to User3.
Also, the UserIds need to maintain the order in which they were originally entered in, which is why I have a
Row_Number() OVER (ORDER BY (SELECT 1)) in the User's table
Is there a way of joining these tables in a single query so I won't need to use a temp table and variable?
I'm using SQL Server 2005