TSQL to allocate employees
-
Hello,
I have a table of guests that our employees call to invite to our activities:
tblGuest
Guestid integer (primary key)
Fname varchar(20)
Lname varchar(20)
Date datetime
employeeid integer
We also have an employee table
tblEmployee
employeeid integer (primary key)
Fname varchar(20)
Lname varchar(20)
employeecode varchar(30)
We have about 100,000 records. Each guest is already assigned an employee.
Employee a has 13,000 records.
Employee b has 14,000 records and so on.
When an employee leaves, we need to reassign that employees guest
update tblGuest
set employeeid = 2
where employeeid = 1
Works well.
But my boss now wants to divide the workforce and hire more folks.
So now I need to allocate 4 new employees to have the guests of a departing employee.
I need some TSQL to allocate the guests.
1) I need to take 100% of the guests and give to an employee if the boss choose to transfer to one employee
(Take all of their guests and give it to this new employee)
2) I need to take half of the guests and give it to two new if the boss wants to transfer the guests to two employees
(Take all of the guest belong to Employee A (employeeid 1) and give them to Employee B (employeeid 2) and Employee C (employeeid 3)
I was thinking of using a Select top, but then that is random. Some selected for Employee B might be selected for Employee C during second select Top statement.
Not sure how to write this in TSQL.
Any help is appreciated.
Thanks
Things will work out. Get back up, change some parameters and recode.
-
How would you identify the employees to whom the guests-of-parting-employee will be handed ? Is there an algorithm for that? Say if Employee ID 1 quits, how will u finalize if EmployeeID 2 and EmployeeID 3 will get EmployeeID 1's guests ?
-
Maybe using NTILE
with cte as (select Guestid,Fname,Lname,Date,employeeid,
ntile(3) over(order by Date) as grp
from tblGuest
where employeeid = 1
)
update cte
set employeeid = case when grp=1 then 2
when grp=2 then 3
when grp=3 then 4
end;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Of course, as most of my challenges, it is the discretion of the boss.
I plan to present him a listbox showing all employees. Then let him select the employees with whom to share the guests.
So he can:
1) pick the FROM employee (example - employee a)
2) then he can pick the TO employess (example - employee f and employee m)
Then I need to write a sql statement that will take all the guests from employee a and allocate them evenly to employee f and employee m (per example above).
LOL. There is no algorithm with my boss!
Thanks for your help.
Things will work out. Get back up, change some parameters and recode.
-
This seems to be a good challenge, but dint provide us any sample data to get started readily.. Hmmm.. i take it on mine, and here are the sample data for any of you who are working on this:
-- Conditionally drop the tables if they should exist
IF OBJECT_ID('tempdb..#Guest') IS NOT NULL
DROP TABLE #Guest
GO
IF OBJECT_ID('tempdb..#Employee') IS NOT NULL
DROP TABLE #Employee
GO
CREATE TABLE #Guest
(
Guestid INTEGER IDENTITY(1,1) PRIMARY KEY,
Fname VARCHAR(20),
Lname VARCHAR(20),
Date DATETIME,
employeeid INTEGER
)
GO
CREATE TABLE #Employee
(
employeeid INTEGER IDENTITY(1,1) PRIMARY KEY,
Fname VARCHAR(20),
Lname VARCHAR(20),
employeecode VARCHAR(30),
)
GO
INSERT INTO #Guest(Fname , Lname ,Date ,employeeid )
SELECT 'Guest1_Fname', 'Guest1_Lname' , GETDATE(), 1
UNION ALL SELECT 'Guest2_Fname', 'Guest2_Lname' , GETDATE(), 2
UNION ALL SELECT 'Guest3_Fname', 'Guest3_Lname' , GETDATE(), 3
UNION ALL SELECT 'Guest4_Fname', 'Guest4_Lname' , GETDATE(), 1
UNION ALL SELECT 'Guest5_Fname', 'Guest5_Lname' , GETDATE(), 2
UNION ALL SELECT 'Guest6_Fname', 'Guest6_Lname' , GETDATE(), 3
UNION ALL SELECT 'Guest7_Fname', 'Guest7_Lname' , GETDATE(), 4
UNION ALL SELECT 'Guest8_Fname', 'Guest8_Lname' , GETDATE(), 2
UNION ALL SELECT 'Guest9_Fname', 'Guest9_Lname' , GETDATE(), 3
UNION ALL SELECT 'Guest10_Fname', 'Guest10_Lname' , GETDATE(), 4
UNION ALL SELECT 'Guest11_Fname', 'Guest11_Lname' , GETDATE(), 5
UNION ALL SELECT 'Guest12_Fname', 'Guest12_Lname' , GETDATE(), 3
UNION ALL SELECT 'Guest13_Fname', 'Guest13_Lname' , GETDATE(), 4
UNION ALL SELECT 'Guest14_Fname', 'Guest14_Lname' , GETDATE(), 5
UNION ALL SELECT 'Guest15_Fname', 'Guest15_Lname' , GETDATE(), 6
UNION ALL SELECT 'Guest16_Fname', 'Guest16_Lname' , GETDATE(), 4
UNION ALL SELECT 'Guest17_Fname', 'Guest17_Lname' , GETDATE(), 5
UNION ALL SELECT 'Guest18_Fname', 'Guest18_Lname' , GETDATE(), 6
UNION ALL SELECT 'Guest19_Fname', 'Guest19_Lname' , GETDATE(), 7
UNION ALL SELECT 'Guest20_Fname', 'Guest20_Lname' , GETDATE(), 5
UNION ALL SELECT 'Guest21_Fname', 'Guest21_Lname' , GETDATE(), 6
UNION ALL SELECT 'Guest22_Fname', 'Guest22_Lname' , GETDATE(), 7
UNION ALL SELECT 'Guest23_Fname', 'Guest23_Lname' , GETDATE(), 8
UNION ALL SELECT 'Guest24_Fname', 'Guest24_Lname' , GETDATE(), 6
UNION ALL SELECT 'Guest25_Fname', 'Guest25_Lname' , GETDATE(), 7
UNION ALL SELECT 'Guest26_Fname', 'Guest26_Lname' , GETDATE(), 8
UNION ALL SELECT 'Guest27_Fname', 'Guest27_Lname' , GETDATE(), 9
UNION ALL SELECT 'Guest28_Fname', 'Guest28_Lname' , GETDATE(), 7
UNION ALL SELECT 'Guest29_Fname', 'Guest29_Lname' , GETDATE(), 8
UNION ALL SELECT 'Guest30_Fname', 'Guest30_Lname' , GETDATE(), 9
UNION ALL SELECT 'Guest31_Fname', 'Guest31_Lname' , GETDATE(), 10
UNION ALL SELECT 'Guest32_Fname', 'Guest32_Lname' , GETDATE(), 8
UNION ALL SELECT 'Guest33_Fname', 'Guest33_Lname' , GETDATE(), 9
UNION ALL SELECT 'Guest34_Fname', 'Guest34_Lname' , GETDATE(), 10
UNION ALL SELECT 'Guest35_Fname', 'Guest35_Lname' , GETDATE(), 11
UNION ALL SELECT 'Guest36_Fname', 'Guest36_Lname' , GETDATE(), 9
UNION ALL SELECT 'Guest37_Fname', 'Guest37_Lname' , GETDATE(), 10
UNION ALL SELECT 'Guest38_Fname', 'Guest38_Lname' , GETDATE(), 11
UNION ALL SELECT 'Guest39_Fname', 'Guest39_Lname' , GETDATE(), 12
UNION ALL SELECT 'Guest40_Fname', 'Guest40_Lname' , GETDATE(), 10
UNION ALL SELECT 'Guest41_Fname', 'Guest41_Lname' , GETDATE(), 11
UNION ALL SELECT 'Guest42_Fname', 'Guest42_Lname' , GETDATE(), 12
UNION ALL SELECT 'Guest43_Fname', 'Guest43_Lname' , GETDATE(), 13
UNION ALL SELECT 'Guest44_Fname', 'Guest44_Lname' , GETDATE(), 11
UNION ALL SELECT 'Guest45_Fname', 'Guest45_Lname' , GETDATE(), 12
UNION ALL SELECT 'Guest46_Fname', 'Guest46_Lname' , GETDATE(), 13
UNION ALL SELECT 'Guest47_Fname', 'Guest47_Lname' , GETDATE(), 14
UNION ALL SELECT 'Guest48_Fname', 'Guest48_Lname' , GETDATE(), 12
UNION ALL SELECT 'Guest49_Fname', 'Guest49_Lname' , GETDATE(), 13
UNION ALL SELECT 'Guest50_Fname', 'Guest50_Lname' , GETDATE(), 14
UNION ALL SELECT 'Guest51_Fname', 'Guest51_Lname' , GETDATE(), 15
UNION ALL SELECT 'Guest52_Fname', 'Guest52_Lname' , GETDATE(), 13
UNION ALL SELECT 'Guest53_Fname', 'Guest53_Lname' , GETDATE(), 14
UNION ALL SELECT 'Guest54_Fname', 'Guest54_Lname' , GETDATE(), 15
UNION ALL SELECT 'Guest55_Fname', 'Guest55_Lname' , GETDATE(), 16
UNION ALL SELECT 'Guest56_Fname', 'Guest56_Lname' , GETDATE(), 14
UNION ALL SELECT 'Guest57_Fname', 'Guest57_Lname' , GETDATE(), 15
UNION ALL SELECT 'Guest58_Fname', 'Guest58_Lname' , GETDATE(), 16
UNION ALL SELECT 'Guest59_Fname', 'Guest59_Lname' , GETDATE(), 17
UNION ALL SELECT 'Guest60_Fname', 'Guest60_Lname' , GETDATE(), 15
UNION ALL SELECT 'Guest61_Fname', 'Guest61_Lname' , GETDATE(), 16
UNION ALL SELECT 'Guest62_Fname', 'Guest62_Lname' , GETDATE(), 17
UNION ALL SELECT 'Guest63_Fname', 'Guest63_Lname' , GETDATE(), 18
UNION ALL SELECT 'Guest64_Fname', 'Guest64_Lname' , GETDATE(), 16
UNION ALL SELECT 'Guest65_Fname', 'Guest65_Lname' , GETDATE(), 17
UNION ALL SELECT 'Guest66_Fname', 'Guest66_Lname' , GETDATE(), 18
UNION ALL SELECT 'Guest67_Fname', 'Guest67_Lname' , GETDATE(), 19
UNION ALL SELECT 'Guest68_Fname', 'Guest68_Lname' , GETDATE(), 17
UNION ALL SELECT 'Guest69_Fname', 'Guest69_Lname' , GETDATE(), 18
UNION ALL SELECT 'Guest70_Fname', 'Guest70_Lname' , GETDATE(), 19
UNION ALL SELECT 'Guest71_Fname', 'Guest71_Lname' , GETDATE(), 20
UNION ALL SELECT 'Guest72_Fname', 'Guest72_Lname' , GETDATE(), 18
UNION ALL SELECT 'Guest73_Fname', 'Guest73_Lname' , GETDATE(), 19
UNION ALL SELECT 'Guest74_Fname', 'Guest74_Lname' , GETDATE(), 20
UNION ALL SELECT 'Guest75_Fname', 'Guest75_Lname' , GETDATE(), 21
UNION ALL SELECT 'Guest76_Fname', 'Guest76_Lname' , GETDATE(), 19
UNION ALL SELECT 'Guest77_Fname', 'Guest77_Lname' , GETDATE(), 20
UNION ALL SELECT 'Guest78_Fname', 'Guest78_Lname' , GETDATE(), 21
UNION ALL SELECT 'Guest79_Fname', 'Guest79_Lname' , GETDATE(), 22
UNION ALL SELECT 'Guest80_Fname', 'Guest80_Lname' , GETDATE(), 20
UNION ALL SELECT 'Guest81_Fname', 'Guest81_Lname' , GETDATE(), 21
UNION ALL SELECT 'Guest82_Fname', 'Guest82_Lname' , GETDATE(), 22
UNION ALL SELECT 'Guest83_Fname', 'Guest83_Lname' , GETDATE(), 23
UNION ALL SELECT 'Guest84_Fname', 'Guest84_Lname' , GETDATE(), 21
UNION ALL SELECT 'Guest85_Fname', 'Guest85_Lname' , GETDATE(), 22
UNION ALL SELECT 'Guest86_Fname', 'Guest86_Lname' , GETDATE(), 23
UNION ALL SELECT 'Guest87_Fname', 'Guest87_Lname' , GETDATE(), 24
UNION ALL SELECT 'Guest88_Fname', 'Guest88_Lname' , GETDATE(), 22
UNION ALL SELECT 'Guest89_Fname', 'Guest89_Lname' , GETDATE(), 23
UNION ALL SELECT 'Guest90_Fname', 'Guest90_Lname' , GETDATE(), 24
UNION ALL SELECT 'Guest91_Fname', 'Guest91_Lname' , GETDATE(), 25
UNION ALL SELECT 'Guest92_Fname', 'Guest92_Lname' , GETDATE(), 23
UNION ALL SELECT 'Guest93_Fname', 'Guest93_Lname' , GETDATE(), 24
UNION ALL SELECT 'Guest94_Fname', 'Guest94_Lname' , GETDATE(), 25
UNION ALL SELECT 'Guest95_Fname', 'Guest95_Lname' , GETDATE(), 26
UNION ALL SELECT 'Guest96_Fname', 'Guest96_Lname' , GETDATE(), 24
UNION ALL SELECT 'Guest97_Fname', 'Guest97_Lname' , GETDATE(), 25
UNION ALL SELECT 'Guest98_Fname', 'Guest98_Lname' , GETDATE(), 26
UNION ALL SELECT 'Guest99_Fname', 'Guest99_Lname' , GETDATE(), 27
INSERT INTO #Employee(Fname , Lname ,employeecode )
SELECT 'Emp1_Fname', 'Emp1_Lname', 'EID_1'
UNION ALL SELECT 'Emp2_Fname', 'Emp2_Lname', 'EID_2'
UNION ALL SELECT 'Emp3_Fname', 'Emp3_Lname', 'EID_3'
UNION ALL SELECT 'Emp4_Fname', 'Emp4_Lname', 'EID_4'
UNION ALL SELECT 'Emp5_Fname', 'Emp5_Lname', 'EID_5'
UNION ALL SELECT 'Emp6_Fname', 'Emp6_Lname', 'EID_6'
UNION ALL SELECT 'Emp7_Fname', 'Emp7_Lname', 'EID_7'
UNION ALL SELECT 'Emp8_Fname', 'Emp8_Lname', 'EID_8'
UNION ALL SELECT 'Emp9_Fname', 'Emp9_Lname', 'EID_9'
UNION ALL SELECT 'Emp10_Fname', 'Emp10_Lname', 'EID_10'
UNION ALL SELECT 'Emp11_Fname', 'Emp11_Lname', 'EID_11'
UNION ALL SELECT 'Emp12_Fname', 'Emp12_Lname', 'EID_12'
UNION ALL SELECT 'Emp13_Fname', 'Emp13_Lname', 'EID_13'
UNION ALL SELECT 'Emp14_Fname', 'Emp14_Lname', 'EID_14'
UNION ALL SELECT 'Emp15_Fname', 'Emp15_Lname', 'EID_15'
UNION ALL SELECT 'Emp16_Fname', 'Emp16_Lname', 'EID_16'
UNION ALL SELECT 'Emp17_Fname', 'Emp17_Lname', 'EID_17'
UNION ALL SELECT 'Emp18_Fname', 'Emp18_Lname', 'EID_18'
UNION ALL SELECT 'Emp19_Fname', 'Emp19_Lname', 'EID_19'
UNION ALL SELECT 'Emp20_Fname', 'Emp20_Lname', 'EID_20'
UNION ALL SELECT 'Emp21_Fname', 'Emp21_Lname', 'EID_21'
UNION ALL SELECT 'Emp22_Fname', 'Emp22_Lname', 'EID_22'
UNION ALL SELECT 'Emp23_Fname', 'Emp23_Lname', 'EID_23'
UNION ALL SELECT 'Emp24_Fname', 'Emp24_Lname', 'EID_24'
UNION ALL SELECT 'Emp25_Fname', 'Emp25_Lname', 'EID_25'
UNION ALL SELECT 'Emp26_Fname', 'Emp26_Lname', 'EID_26'
UNION ALL SELECT 'Emp27_Fname', 'Emp27_Lname', 'EID_27'
-
Thanks.
I didn't think to give sample data. But I appreciate any help at all.
I can do this in VBA by start from the top and going row by row. I suppose a cursor would do the same.
Maybe I need to create a stored procedure with temp tables, but I still can't figure it out.
Thanks again for the help.
Things will work out. Get back up, change some parameters and recode.
-
Hi there, your request made me sweat for 2 hrs 😉
Take this, this will allocate the employees your boss chose in replacement of the departing employee.. this maintains randomness ; so your boss can pick any number of employees and any employee to fill the departing employee's shoes.
The comments in the code will help you understand what the code does; if it doesn, dont worry, i am always here to answer 🙂
Following the example tables i have given, here is the code for your request
--: Assign local variable
DECLARE @DepartingEmployeeID INT
DECLARE @AssignedEmployeeIDS VARCHAR(1000)
DECLARE @CountOfAssignedEmpIDs INT
DECLARE @GroupID INT
DECLARE @MinIndex INT
DECLARE @MaxIndex INT
--: Here is where you get the input from your boss ; Please note 2 main things here
-- 1. The employees to whom your boss is going to allocate the guests (guests of the departing employee)
-- must be prepared as a comma-seperated list
-- 2. The list MUST be comma-seperated
SET @DepartingEmployeeID = 1
SET @AssignedEmployeeIDS = '5,6,7'
-- See what all guests that fall under the departing employee
SELECT * FROM #Guest WHERE employeeid = @DepartingEmployeeID
-- This is for testing purpose; you can remove this from your original code
-- Test purpose data starts here
DECLARE @tab TABLE ( GID INT)
INSERT @tab SELECT Guestid FROM #Guest WHERE employeeid = @DepartingEmployeeID
-- Test purpose data ends here
-- Create a temp table to hold the values employees to assing guests (guests of the departing employee)
IF OBJECT_ID('tempdb..#SelectedEmployeesToShare') IS NOT NULL
DROP TABLE #SelectedEmployeesToShare
CREATE TABLE #SelectedEmployeesToShare
(
RID INT IDENTITY(1,1) PRIMARY KEY,
EmployeeID INT
)
-- This is place where we split the comma-separated list into individual elements
-- This has been much discussed in this thread:
-- http://www.sqlservercentral.com/Forums/Topic988784-391-1.aspx
-- I personally follow Jeff's method of splitting CSVs, but here, to avoid a conversion of
-- VARCHAR to INT , i used Brad's
INSERT INTO #SelectedEmployeesToShare (EmployeeID)
SELECT x.i.value('(./text())[1]','INT') AS EmployeeID
FROM (
SELECT XMLList=CAST('<i>'+REPLACE(@AssignedEmployeeIDS,',','</i><i>')+'</i>' AS XML).query('.')
) a
CROSS APPLY
XMLList.nodes('i') x(i)
-- Get the count of employees your boss chose to share the guests-list
SELECT @CountOfAssignedEmpIDs = COUNT(*)
FROM #SelectedEmployeesToShare
-- create a temp sub-set of the data ; i.e., the guests belonging to the departing employee
IF OBJECT_ID('tempdb..#TempGuests') IS NOT NULL
DROP TABLE #TempGuests
CREATE TABLE #TempGuests
(
RID INT,
GroupID INT,
GuestID INT,
Fname VARCHAR(20),
Lname VARCHAR(20),
Date DATETIME,
employeeid INTEGER
)
-- THis is where we update some data
;WITH cte0 AS
(
SELECT
-- Assign virtual row numbers to the list
Sl_No = ROW_NUMBER() OVER ( ORDER BY Guestid )
,Guestid ,Fname , Lname ,Date ,NULL AS employeeid
FROM #Guest
WHERE employeeid = @DepartingEmployeeID
),
cte1 AS
(
SELECT
-- Group them based on the count of employees your boss chose
-- THis will be used in the logic where we update the chosen employees
GroupID =( ((Sl_No - 1) / @CountOfAssignedEmpIDs) + 1 )
,Guestid ,Fname , Lname ,Date ,NULL AS employeeid
FROM cte0
),
cte2 AS
(
SELECT
-- THis is where we allocate a row number based on the Group
-- It will help us in the Loop
RID = ROW_NUMBER() OVER ( PARTITION BY GroupID ORDER BY GroupID ,Guestid )
,GroupID,Guestid ,Fname , Lname ,Date
,NULL AS employeeid -- This is intentionally made as NULL;
-- it will be updated with the new empids in the loop
FROM cte1
)
INSERT INTO #TempGuests
SELECT RID ,GroupID , Guestid ,Fname , Lname ,Date ,employeeid FROM cte2
-- Loop parameters
SELECT @MinIndex = MIN(GroupID) FROM #TempGuests
SELECT @MaxIndex = MAX(GroupID) FROM #TempGuests
-- Loop Start
WHILE(@MinIndex <= @MaxIndex)
BEGIN
/*
This is our main logic; the logic goes like this
1. Take the first group
2. Update the RID of the first group (from #TempGuests)
with the RID of (#SelectedEmployeesToShare)
3. The above step will update the NULL we initially assinged
with a set of EmployeeIDs
4. Increment the loop and perform operations 2 and 3
*/
UPDATE TG
SET TG.employeeid = Emp.EmployeeID
FROM #TempGuests TG
INNER JOIN #SelectedEmployeesToShare Emp
ON TG.RID = Emp.RID
WHERE TG.GroupID = @MinIndex
SELECT @MinIndex = @MinIndex + 1
END
--SELECT * FROM #TempGuests
-- Now as teh subset is updated with Emp IDs, update the source table
UPDATE G
SET G.employeeid = TG.EmployeeID
FROM #Guest G
INNER JOIN #TempGuests TG
ON G.Guestid = TG.Guestid
-- This is the testing temp table where we created;
-- The result of the below query should match the result of the
-- query we ran that was under this comment
-- "-- See what all guests that fall under the departing employee"
SELECT * FROM #Guest WHERE Guestid in (select gid from @tab)
Hope this helps. Get back to me if u need any clarification!
-
Whoa!
I am so blown away. Thank you.
It is going to take me a bit to go through this. I would say pretty advanced coding here.
Some of the code you wrote, I have never used. But, now is a good time to learn.
Again, thanks.
Things will work out. Get back up, change some parameters and recode.
-
WebTechie38 (9/20/2010)
Whoa!I am so blown away. Thank you.
It is going to take me a bit to go through this. I would say pretty advanced coding here.
Some of the code you wrote, I have never used. But, now is a good time to learn.
Again, thanks.
Not a problem, Techie.. with the ready-to-use data around, lets hope to see a good number of gurus and giants seeing the thread and working on it 🙂
-
This tweaked my interest on the train journey home this evening. Extension/merge of the two previous suggestions, using the script to generate data that ColdCoffee kindly added but using perm tables rather than temp.
DECLARE @DepartingEmployeeId INT
DECLARE @TargetEmployeeIdList VARCHAR(1000)
SET @DepartingEmployeeId = 10
SET @TargetEmployeeIdList = '5,6,7,8'
/*
SELECT
*
FROM
dbo.Guest
WHERE
EmployeeID = @DepartingEmployeeId
*/
BEGIN TRANSACTION
;WITH empCTE AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY NEWID()) AS RowNum
, x.i.value('(./text())[1]','INT') AS EmployeeId
FROM
(SELECT XMLList=CAST('<i>'+REPLACE(@TargetEmployeeIdList,',','</i><i>')+'</i>' AS XML).query('.')) a
CROSS APPLY
XMLList.nodes('i') x(i)
)
, splitCTE AS
(
SELECT
GuestId
, Fname
, Lname
, NTILE((SELECT COUNT(*) FROM empCTE)) OVER(ORDER BY GuestID) AS SplitGroup
FROM
dbo.Guest
WHERE
EmployeeID = @DepartingEmployeeId
),
targetCTE AS
(
SELECT
s.GuestId
, e.EmployeeId AS newEmployeeId
FROM
splitCTE s
INNER JOIN
empCTE e
ONe.RowNum = s.SplitGroup
)
UPDATE
dbo.Guest
SET
EmployeeId = newEmployeeId
FROM
dbo.Guest g
INNER JOIN
targetCTE t
ONt.GuestId = g.GuestId
/*
SELECT
*
FROM
dbo.Guest
*/
ROLLBACK TRANSACTION
-
Thanks Mark,
I don't understand the CTE's that you guys are using. I assume you are using Common table expressions. I just looked up something on Microsoft's forum.
Tony
Things will work out. Get back up, change some parameters and recode.
-
The common table expressions used are an alternative to using temporary or derived tables, hopefully making the query easier to understand by breaking it down into smaller parts. Hastily put together example of this re-written to a single query:
DECLARE @DepartingEmployeeId INT
DECLARE @TargetEmployeeIdList VARCHAR(1000)
DECLARE @TargetEmployeeCount INT
SET @DepartingEmployeeId = 12
SET @TargetEmployeeIdList = '5,6,7,8'
-- Cheat to get the number of EmployeeIDs in the list (assuming CSV no ",," entries in CSV)
SET @TargetEmployeeCount = (LEN(@TargetEmployeeIdList) - LEN(REPLACE(@TargetEmployeeIdList, ',', '')) + 1)
BEGIN TRANSACTION
SELECT
*
FROM
dbo.Guest
WHERE
EmployeeID = @DepartingEmployeeID
UPDATE
dbo.Guest
SET
EmployeeId = emp.EmployeeId
FROM
dbo.Guest g
INNER JOIN
(
SELECT
GuestId
, Fname
, Lname
, NTILE(@TargetEmployeeCount) OVER(ORDER BY GuestID) AS SplitGroup
FROM
dbo.Guest
WHERE
EmployeeID = @DepartingEmployeeId
) split
ONsplit.GuestID = g.GuestID
INNER JOIN
(
SELECT
ROW_NUMBER() OVER (ORDER BY NEWID()) AS RowNum
, x.i.value('(./text())[1]','INT') AS EmployeeId
FROM
(SELECT XMLList=CAST('<i>'+REPLACE(@TargetEmployeeIdList,',','</i><i>')+'</i>' AS XML).query('.')) a
CROSS APPLY
XMLList.nodes('i') x(i)
) emp
ONemp.RowNum = split.SplitGroup
SELECT
*
FROM
dbo.Guest
ROLLBACK TRANSACTION
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply