September 16, 2008 at 3:40 pm
Ok, so ... all I'm looking to do is randomly generate teams of two based on employee name. I have 50 records and I just want to randomly group them into a team number. 25 groups of 2.
Any suggestions? What I have will work, but requires manual intervention with the case ... I'd like to figure out a better way to do this ...
This is what I have so far ...
;WITH [EmpTeams] AS
(
SELECT
LTRIM(RTRIM(hp.[p_fname])) + ' ' + LTRIM(RTRIM(hp.[p_lname]))AS [EmpName]
,ht.[fdesc]AS [DeptName]
FROM [dbo].[tbl1] hp
JOIN [dbo].[tbl2] ht
ON hp.[p_level2] = ht.
)
SELECT
CASE
WHEN [RowNumber] = 1 OR [RowNumber] = 2 THEN 1
WHEN [RowNumber] = 3 OR [RowNumber] = 4 THEN 2
WHEN [RowNumber] = 5 OR [RowNumber] = 6 THEN 3
WHEN [RowNumber] = 7 OR [RowNumber] = 8 THEN 4
WHEN [RowNumber] = 9 OR [RowNumber] = 10 THEN 5
WHEN [RowNumber] = 11 OR [RowNumber] = 12 THEN 6
WHEN [RowNumber] = 13 OR [RowNumber] = 14 THEN 7
WHEN [RowNumber] = 15 OR [RowNumber] = 16 THEN 8
WHEN [RowNumber] = 17 OR [RowNumber] = 18 THEN 9
WHEN [RowNumber] = 19 OR [RowNumber] = 20 THEN 10
WHEN [RowNumber] = 21 OR [RowNumber] = 22 THEN 11
WHEN [RowNumber] = 23 OR [RowNumber] = 24 THEN 12
WHEN [RowNumber] = 25 OR [RowNumber] = 26 THEN 13
WHEN [RowNumber] = 27 OR [RowNumber] = 28 THEN 14
WHEN [RowNumber] = 29 OR [RowNumber] = 30 THEN 15
WHEN [RowNumber] = 31 OR [RowNumber] = 32 THEN 16
WHEN [RowNumber] = 33 OR [RowNumber] = 34 THEN 17
WHEN [RowNumber] = 35 OR [RowNumber] = 36 THEN 18
WHEN [RowNumber] = 37 OR [RowNumber] = 38 THEN 19
WHEN [RowNumber] = 39 OR [RowNumber] = 40 THEN 20
WHEN [RowNumber] = 41 OR [RowNumber] = 42 THEN 21
WHEN [RowNumber] = 43 OR [RowNumber] = 44 THEN 22
WHEN [RowNumber] = 45 OR [RowNumber] = 46 THEN 23
WHEN [RowNumber] = 47 OR [RowNumber] = 48 THEN 24
WHEN [RowNumber] = 49 OR [RowNumber] = 50 THEN 25
ELSE 0
END AS [Team]
,[EmpName]
,[DeptName]
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY NEWID()) AS [RowNumber]
,[EmpName]
,[DeptName]
FROM [EmpTeams]
) t
ORDER BY 1
Thanks
September 16, 2008 at 9:49 pm
😀 Hi there,
Hope this helps... 😉
--Let's first create our sample data
DECLARE @tbl TABLE
(
IDINT
)
DECLARE @i INT
SET @i=1
WHILE @i<=50
BEGIN
INSERT INTO @tbl
VALUES(@i)
SET @i=@i+1
END
-- create temporary tables for teams
DECLARE @teamA TABLE
(
IDINT
)
DECLARE @teamB TABLE
(
IDINT
)
-- Randomly distribute 25 players for each team
INSERT INTO @teamA
SELECT TOP 25 ID FROM @tbl
ORDER BY NEWID()
INSERT INTO @teamB
SELECT ID FROM @tbl
WHERE ID NOT IN (SELECT ID FROM @teamA)
-- Check members of the two teams
SELECT * FROM @teamA
SELECT * FROM @teamB
IF EXISTS(SELECT ID FROM @teamA
WHERE ID IN (SELECT ID FROM @teamB))
BEGIN
SELECT 'Identcal ID''s found!'
END
ELSE
BEGIN
SELECT 'NO Identcal ID''s found!'
END
-- TADA!!!
Please tell me if this post was helpful or needs some modifications. Thanks... (o^_')
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
September 18, 2008 at 6:43 am
Replace the CASE statement with:
([ROWNUMBER]/2 + 1 ) AS [Team]
from BOL
If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.
HTH
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
September 18, 2008 at 8:59 am
Terri (9/18/2008)
Replace the CASE statement with:([ROWNUMBER]/2 + 1 ) AS [Team]
from BOL
If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.
HTH
Perfect, thanks!
September 18, 2008 at 9:07 am
Adam Bean (9/18/2008)
Terri (9/18/2008)
Replace the CASE statement with:([ROWNUMBER]/2 + 1 ) AS [Team]
from BOL
If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.
HTH
Perfect, thanks!
Oops, not perfect! It should be
([ROWNUMBER + 1]/2 ) AS [Team] 🙂
but you get the idea.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
September 18, 2008 at 9:10 am
Did you mean? ([ROWNUMBER] + 1)/2 AS [Team]
September 18, 2008 at 9:13 am
Adam Bean (9/18/2008)
Did you mean? ([ROWNUMBER] + 1)/2 AS [Team]
Yes! Fatfingered it again!
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
October 7, 2008 at 2:26 pm
Hey Terri, any thoughts on how to add some better logic in by randomizing the deptname a little better to avoid same deptname groups (teams)?
Thanks
October 8, 2008 at 10:09 am
Adam Bean (10/7/2008)
Hey Terri, any thoughts on how to add some better logic in by randomizing the deptname a little better to avoid same deptname groups (teams)?Thanks
Well, it IS random since you are ordering by NEWID(). Every time you run the code you get a different set of teams with different deptnames. It sounds like you are trying to UNrandomize the selection of teams.
Terri
To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply