Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Employee job mapping


Employee job mapping

Author
Message
preetham gowda
preetham gowda
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 151
I have 2 tables. One with employee location, role, requirement id (to be updated from second table) and second with requirement id, location and role. I need to update employee table with a requirement id.

Limitation is that an employee can fit into multiple requirement and vice versa. But I need one to one mapping.

For e.g.: I have employee a, b, c and demand 1, 2, 3, 4. I should create mapping like a->2, b->1, c->4. Even though employee - b can fit 1 and 4, but "b" should be either 1 or 4 and not both. Same holds good for demands.

Please suggest.
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3003 Visits: 5007
Please post the table structure, sample data and the desired output in a readily consumable format. I am sure you will get help immediately. Your explanation and example is quite unclear. So, please HELP US TO HELP YOU and have a look at the link in my signature to know how to post to get faster answers. I am sure it will be worth the effort.


Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
preetham gowda
preetham gowda
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 151
In the following query I need to make it work without WHILE loop.

Create Table Job (JobID int identity, Role char(10), Location char(50))
Create Table Employee (EmpID int identity, EmpName char(50), Role char(10), Location char(50), JobID int)

Insert into Job (Role, Location) Values ('SE', 'Bangalore')
Insert into Job (Role, Location) Values ('SE', 'Bangalore')
Insert into Job (Role, Location) Values ('SE', 'Pune')
Insert into Job (Role, Location) Values ('TL', 'Bangalore')
Insert into Job (Role, Location) Values ('TL', 'Pune')

Insert into Employee (EmpName, Role, Location) Values ('James', 'SE', 'Bangalore')
Insert into Employee (EmpName, Role, Location) Values ('Jaks', 'SE', 'Pune')
Insert into Employee (EmpName, Role, Location) Values ('Richard', 'SE', 'Delhi')
Insert into Employee (EmpName, Role, Location) Values ('Roshan', 'TL', 'Pune')
Insert into Employee (EmpName, Role, Location) Values ('Vicky', 'TL', 'Pune')

Set NoCount On

Declare @intMin int, @intMax int
Declare @intJobID int

Select @intMin = Min(EmpID), @intMax = Max(EmpID) From Employee

While (@intMin <= @intMax)
Begin
Select
@intJobID = Job.JobID
From
Job
Inner Join
Employee
On
Employee.EmpID = @intMin And
Job.Role = Employee.Role And
Job.Location = Employee.Location
Where
Not Exists (Select Null From Employee Emp_Tmp Where Emp_Tmp.JobID = Job.JobID)
Order By Job.JobID Desc

If (@intJobID is not null)
Begin
Update Employee Set JobID = @intJobID Where EmpID = @intMin
End

Set @intJobID = null

Set @intMin = @intMin + 1
End

Select * From Employee

Drop Table Job
Drop Table Employee
Kingston Dhasian
Kingston Dhasian
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3003 Visits: 5007
Try this

Create Table Job (JobID int identity, Role char(10), Location char(50))
Create Table Employee (EmpID int identity, EmpName char(50), Role char(10), Location char(50), JobID int)

Insert into Job (Role, Location) Values ('SE', 'Bangalore')
Insert into Job (Role, Location) Values ('SE', 'Bangalore')
Insert into Job (Role, Location) Values ('SE', 'Pune')
Insert into Job (Role, Location) Values ('TL', 'Bangalore')
Insert into Job (Role, Location) Values ('TL', 'Pune')

Insert into Employee (EmpName, Role, Location) Values ('James', 'SE', 'Bangalore')
Insert into Employee (EmpName, Role, Location) Values ('Jaks', 'SE', 'Pune')
Insert into Employee (EmpName, Role, Location) Values ('Richard', 'SE', 'Delhi')
Insert into Employee (EmpName, Role, Location) Values ('Roshan', 'TL', 'Pune')
Insert into Employee (EmpName, Role, Location) Values ('Vicky', 'TL', 'Pune')

UPDATE Emp
SET JobID = Job.JobID
FROM (
SELECT ROW_NUMBER() OVER ( PARTITION BY Role, Location ORDER BY EmpID ) Row, *
FROM Employee
) Emp
INNER JOIN (
SELECT ROW_NUMBER() OVER ( PARTITION BY Role, Location ORDER BY JobID ) Row, *
FROM Job
) Job
ON Emp.Role = Job.Role AND Emp.Location = Job.Location AND Emp.Row = Job.Row

SELECT * FROM Employee

DROP TABLE Job
DROP TABLE Employee




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
preetham gowda
preetham gowda
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 151
Thanks dude. It worked for me
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search