June 3, 2010 at 10:48 pm
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.
June 3, 2010 at 10:58 pm
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.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2010 at 5:31 am
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
June 4, 2010 at 7:11 am
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')
UPDATEEmp
SETJobID = Job.JobID
FROM(
SELECTROW_NUMBER() OVER ( PARTITION BY Role, Location ORDER BY EmpID ) Row, *
FROMEmployee
) 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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 7, 2010 at 12:35 am
Thanks dude. It worked for me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply