Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Employee job mapping Expand / Collapse
Author
Message
Posted Thursday, June 3, 2010 10:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 1, 2013 1:04 AM
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.
Post #932572
Posted Thursday, June 3, 2010 10:58 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 2,682, Visits: 4,745
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/
Post #932578
Posted Friday, June 4, 2010 5:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 1, 2013 1:04 AM
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
Post #932724
Posted Friday, June 4, 2010 7:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:17 AM
Points: 2,682, Visits: 4,745
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/
Post #932791
Posted Monday, June 7, 2010 12:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 1, 2013 1:04 AM
Points: 30, Visits: 151
Thanks dude. It worked for me
Post #933346
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse