Choosing Random Users - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Choosing Random Users

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2010-06-13 : 01:20:17

I have a table with 10 usernames. Out of them, I want to choose 5 users everyday, with one as the Editor and 4 contributors

If one user is selected as Editor for a week, he/she cannot become editor again during the sme week.


	Editor	Cont1	Cont2	Cont3	Cont4
20-Jun 	 U1	U8	 U9	U3	U4
21-Jun	 U7	U2	U5	U6	U10
22-Jun	 U3	U4	U9	U2	U8
23-Jun   U4     U8      U3      U5      U2
and so on..

Sample Data

DECLARE @Users Table (Username varchar(3))
INSERT INTO @Users VALUES ('U1')
INSERT INTO @Users VALUES ('U2')
INSERT INTO @Users VALUES ('U3')
INSERT INTO @Users VALUES ('U4')
INSERT INTO @Users VALUES ('U5')
INSERT INTO @Users VALUES ('U6')
INSERT INTO @Users VALUES ('U7') 
INSERT INTO @Users VALUES ('U8')
INSERT INTO @Users VALUES ('U9')
INSERT INTO @Users VALUES ('U10')

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-13 : 12:51:07

With the given information I can give that:

-- 4 contributors
select top 4 username from usertable order by newid()

-- 1 editor
select top 1 username from usertable ut
where not exists(select * from destinationtable d where ut.username=d.username and theDate between dateadd(dd,-7,getdate()) and getdate())
order by newid()

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-15 : 05:07:41

And "the same week" means what?
Same ISO week number? Other definition of week?

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-17 : 02:52:49

[code]
declare @date datetime

select @date = '2010-06-20'

; with user_editor
as
(
select username,
editor = case when exists
(
select *
from job x
where x.editor = u.username
and x.[date] >= dateadd(day, -7, @date)
and x.[date] < @date
)
then 'N'
else 'Y'
end
from users u
),
users
as
(
select username, row_no = row_number() over (order by case when editor = 'Y' then 1 else 2 end, newid())
from user_editor
)
insert into job([date], editor, cont1, cont2, cont3, cont4)
select @date, [1], [2], [3], [4], [5]
from (
select username, row_no
from users
where row_no <= 5
) e
pivot
(
max(username)
for row_no in ([1], [2], [3], [4], [5])
) p
[/code]

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

lols
Posting Yak Master

174 Posts

Posted - 2010-06-17 : 06:25:58

khtan thanks for your time, but I am unable to compile the query. What is the sample data assumed? Also what will I have to change when after a few months, I have 1 Editor and 10 contribs? The problem statement changes as 'If one user is selected as Editor for a week, he/she cannot become editor again till all the others have got a chance to become an Editor'..so it moves in a cyclic fashion.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-17 : 09:14:02

what is the error you encounter ?

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

lols
Posting Yak Master

174 Posts

Posted - 2010-06-17 : 10:30:11

Invalid object name 'job' and 4 other errors

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources