August 12, 2015 at 7:16 am
create table #temp(id int,logonid varchar(15),reviewer varchar(15))
insert into #temp select 1,'personA',null
insert into #temp select 2,'personB',null
insert into #temp select 3,'personC',null
insert into #temp select 4,'personD',null
insert into #temp select 5,'personE',null
insert into #temp select 6,'personF',null
I have a table with 6 people in it. I need to randomly assign a different person as a reviewer to each Logonid.
Thanks in advance for any help you can provide.
August 12, 2015 at 7:44 am
Are you missing information? Where do you get the persons that will be assigned? Are those the reviewers?
August 12, 2015 at 8:29 am
A different person to each what?
Something like this will create a random order:
SELECT * FROM #temp AS T ORDER BY NEWID();
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 12, 2015 at 9:15 am
Those listed in the Logonid column are the reviewes. So personA might be assigned to personC etc. PersonA obviously can't be assigned as a reviewer to PersonA, that might open the door to some bias!
Sorry for the missing information.
August 12, 2015 at 9:17 am
Luis Cazares (8/12/2015)
Are you missing information? Where do you get the persons that will be assigned? Are those the reviewers?
Those listed in the Logonid column will be the reviewers. So personA might be assigned to personC etc. PersonA obviously can't be assigned as a reviewer to PersonA, that might open the door to some bias!
Sorry for the missing information.
August 12, 2015 at 9:19 am
Jack Corbett (8/12/2015)
A different person to each what?Something like this will create a random order:
SELECT * FROM #temp AS T ORDER BY NEWID();
For example, personA would be assigned as a reviewer to personC. PersonA can't be assigned to personA, in order to avoid a biased review. I think your newid() option could open the door to personA getting assigned to personA, no?
Thanks, sorry for the lack of information.
August 12, 2015 at 9:25 am
Does the assignment need to be balanced?
August 12, 2015 at 9:29 am
Luis Cazares (8/12/2015)
Does the assignment need to be balanced?
Yes. By balanced I'm guessing you mean each Logonid must be assigned as a Reviewer and can only be assigned once.
Thank you.
August 12, 2015 at 10:25 am
This works by creating a "loop" in a random order and assigning the previous person in the loop as the reviewer.
DECLARE @temp TABLE (id int,logonid varchar(15),reviewer varchar(15))
insert into @temp
VALUES(1,'personA',NULL)
,(2,'personB',null)
,(3,'personC',null)
,(4,'personD',null)
,(5,'personE',null)
,(6,'personF',null)
;
WITH CTE AS (
SELECT *
,ROW_NUMBER() OVER(ORDER BY NEWID()) AS rn
FROM @temp
)
, reviewers AS (
SELECT id,
reviewer,
CASE WHEN rn = 1 THEN LAST_VALUE(logonid) OVER(ORDER BY rn ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ELSE LAG(logonid) OVER(ORDER BY rn) END AS new_reviewer
FROM CTE
)
UPDATE reviewers
SET reviewer = new_reviewer
SELECT *
FROM @temp
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 12, 2015 at 10:36 am
drew.allen (8/12/2015)
This works by creating a "loop" in a random order and assigning the previous person in the loop as the reviewer.
DECLARE @temp TABLE (id int,logonid varchar(15),reviewer varchar(15))
insert into @temp
VALUES(1,'personA',NULL)
,(2,'personB',null)
,(3,'personC',null)
,(4,'personD',null)
,(5,'personE',null)
,(6,'personF',null)
;
WITH CTE AS (
SELECT *
,ROW_NUMBER() OVER(ORDER BY NEWID()) AS rn
FROM @temp
)
, reviewers AS (
SELECT id,
reviewer,
CASE WHEN rn = 1 THEN LAST_VALUE(logonid) OVER(ORDER BY rn ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ELSE LAG(logonid) OVER(ORDER BY rn) END AS new_reviewer
FROM CTE
)
UPDATE reviewers
SET reviewer = new_reviewer
SELECT *
FROM @temp
Drew
Beautiful, thank you. I will review your approach for my future reference.
August 12, 2015 at 10:38 am
Nice solution Drew.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 13, 2015 at 4:10 am
Hello Drew,
your solution looks interesting and uses something I didn't know before (BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)... but, unfortunately, all it does for me is :
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'ROWS'.
Since I never used such code, I have no idea how to make it work. Could you help me a bit, please?
Thanks, Vladan
August 13, 2015 at 4:53 am
Vladan (8/13/2015)
Hello Drew,your solution looks interesting and uses something I didn't know before (BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)... but, unfortunately, all it does for me is :
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'ROWS'.
Since I never used such code, I have no idea how to make it work. Could you help me a bit, please?
Thanks, Vladan
Vladan,
Last_value was introduced in 2012.
I'm pretty sure you get the error you got on 2008 R2 and before.
Rodders...
August 13, 2015 at 5:01 am
Thanks, so it is. It must be that heat - I had SQL 2012 Management Studio running, but was connected to 2008 server :hehe:. It works fine on SQLS 2012. Sorry for the confusion.
Vladan
August 13, 2015 at 6:55 am
Do you need help to make the query work on 2008? Or are you fine now? Did you understand the solution?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply