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