Help with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Query

Author

Topic

chemangelo
Starting Member

2 Posts

Posted - 2011-02-22 : 12:43:15

Hello everyone,

I'm not sure if there's a for loop in Sql 2005? Here's what I'm trying to do. I have a query that gives me a list of active members which are reviewers. I want to insert a random name from that query without repeating it (unless all members have already been selected) in a different table every time someone post something for submission. so I want them all to have an equal amount of reviews.

Any ideas?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-02-23 : 01:33:36

Please explain with example...

Vaibhav T

If I cant go back, I want to go fast...

chemangelo
Starting Member

2 Posts

Posted - 2011-02-23 : 13:52:08

Ok, I have a query that returns all active members of an teacher role. Let's say I get 20 teachers.

Students can upload a word document for random teachers to grade. Once a student uploads a paper, I have the following query
SELECT TOP (1) UserID
FROM Users
WHERE (RoleID = '3') AND (Active = '1')
ORDER BY NEWID()

This gives me a random teacher from the list but is not an even number for all papers submitted. So one teacher can get 50 papers and another one only 12.

How can I modify this query so it will select either down the teacher list query without repeating a teacher until everyone has already been selected once and then do it again so all papers are distributed equally. If I have 20 teacher and 60 papers each will get 2. Am I making sense?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-23 : 21:57:55

quote:
If I have 20 teacher and 60 papers each will get 2

You mean each will get 3 ? 60 / 20 = 3

maybe something like this ?


select *
from   Users
       cross join number_table n
where  . . . 
and    n.number <= number_of_papers / number_of_teacher
ORDER BY no_of_paper_assigned, newid()

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources