November 8, 2007 at 12:41 pm
I have a table with about 500 records. I also have an Employee table that holds all our supervisor information. I need to run a process weekly that takes the table of 500 records and randomly, but evenly divides this data among the active supervisors. I'm totally lost as to how to do this, so any help would be appreciated.
Thanks!
November 8, 2007 at 1:16 pm
Since it sounds like the number of supervisors is a variable from week to week, try something like this:
declare @active_supers as int
set @active_supers=10 --just picking a number for illustration
select NTILE(@active_supers) OVER(order by newid()) r ,* from employeetable
You should then have 10 groups of 50 employees each, based on a random order. (look at the r field you get back.)
NTIL(x) will "partition" records into x groups
ORDER by NewID() is a random sort.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 8, 2007 at 1:41 pm
Now I'm very confused. I'm a little new to this. Am I using T-SQL rather than an SSIS data flow? Here's more specifics as I can't quite see what is being done. Maybe with my data it will be easier to understand.
Employee Table (5-7 Supervisors)
EnpID, Title, Dept
QA Table (about 400 rows)
RandomNUmber, EmployeeKey, Dept, AccountNumber
I need to add to the QA table the EmpID of a Supervisor for each account. They need to be evenly distributed and a Supervisor can't have his/her own employee either.
Thanks in advance!
November 8, 2007 at 2:07 pm
The T-SQL above would have given you a split of the data into even chunks, using a random sort. distributing would have been simple (xp_sendmail, a report, etc...)
The requirement you didn't mention the first time is the stinky fly in the ointment. (the can't have their own employees one...kind of a big one...)
hmm. Let me thnk on that one a little more....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply