Update a table using another table value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update a table using another table value

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-07-07 : 02:21:23

dear friends
I have the table with employee Date of Joining details Which may contains some repeated Employee ID, because there is a possibility for employee will be transfered to the other department, and i have another table with the details of Attendence, i want to update the Department code corresponsing to the date in attendance table.

Please find my sample tables below

Table : EmpJoining

EmpId DOJ DepartmentCode
1001 12-05-2010 Dep001
1004 13-05-2010 Dep020
1001 25-05-2010 Dep100
1005 01-06-2010 Dep034

Table : Attendance (Before Exectute Query)

EmpId AttendaceDate DepartmentCode
1001 01-05-2010 NULL
1001 02-05-2010 NULL
1001 03-05-2010 NULL
1001 04-05-2010 NULL
1001 05-05-2010 NULL
1001 06-05-2010 NULL
1001 07-05-2010 NULL
1001 08-05-2010 NULL
1001 09-05-2010 NULL
1001 10-05-2010 NULL
1001 11-05-2010 NULL
1001 12-05-2010 NULL
1001 13-05-2010 NULL
1001 14-05-2010 NULL
1001 15-05-2010 NULL
1001 16-05-2010 NULL
1001 17-05-2010 NULL
1001 18-05-2010 NULL
1001 19-05-2010 NULL
1001 20-05-2010 NULL
1001 21-05-2010 NULL
1001 22-05-2010 NULL
1001 23-05-2010 NULL
1001 24-05-2010 NULL
1001 25-05-2010 NULL
1001 26-05-2010 NULL
1001 27-05-2010 NULL
1001 28-05-2010 NULL
1001 29-05-2010 NULL
1001 30-05-2010 NULL
1001 31-05-2010 NULL

Table : Attendance (After Execute the Output should be like below)

EmpId AttendaceDate DepartmentCode
1001 01-05-2010 NULL
1001 02-05-2010 NULL
1001 03-05-2010 NULL
1001 04-05-2010 NULL
1001 05-05-2010 NULL
1001 06-05-2010 NULL
1001 07-05-2010 NULL
1001 08-05-2010 NULL
1001 09-05-2010 NULL
1001 10-05-2010 NULL
1001 11-05-2010 NULL
1001 12-05-2010 Dep001
1001 13-05-2010 Dep001
1001 14-05-2010 Dep001
1001 15-05-2010 Dep001
1001 16-05-2010 Dep001
1001 17-05-2010 Dep001
1001 18-05-2010 Dep001
1001 19-05-2010 Dep001
1001 20-05-2010 Dep001
1001 21-05-2010 Dep001
1001 22-05-2010 Dep001
1001 23-05-2010 Dep001
1001 24-05-2010 Dep001
1001 25-05-2010 Dep100
1001 26-05-2010 Dep100
1001 27-05-2010 Dep100
1001 28-05-2010 Dep100
1001 29-05-2010 Dep100
1001 30-05-2010 Dep100
1001 31-05-2010 Dep100

For the sample i have used the EmpId 1001, But in the attendace table all other employee id will also available, and the values also should be updated on corresponding Employee Id also..

Note : I want to execute a query and the attendance table need to be updated like the sample which i provided.

Please help me to get the expected output, let me know if you have any questions

sql-programmers
Posting Yak Master

190 Posts

Posted - 2010-07-07 : 06:58:46

Use the script for update ,

update Attendance set Attendance.DepartmentCode=EmpJoining.DepartmentCode
from Attendance inner join EmpJoining on EmpJoining.EmpId=Attendance.EmpId
where EmpJoining.DOJ<=Attendance.AttendaceDate

AND
(CASE WHEN NOT(SELECT MIN(DOJ) FROM EmpJoining AS Emp WHERE Emp.DepartmentCode <> EmpJoining.DepartmentCode AND Emp.EmpId = EmpJoining.EmpId
AND DOJ>=EmpJoining.DOJ)IS NULL THEN
Attendance.AttendaceDate
ELSE 1 END)
<= (CASE WHEN NOT(SELECT MIN(DOJ) FROM EmpJoining AS Emp WHERE Emp.DepartmentCode <> EmpJoining.DepartmentCode AND Emp.EmpId = EmpJoining.EmpId
AND DOJ>=EmpJoining.DOJ)IS NULL THEN
DATEADD(D,-1,(SELECT MIN(DOJ) FROM EmpJoining AS Emp WHERE Emp.DepartmentCode <> EmpJoining.DepartmentCode AND Emp.EmpId = EmpJoining.EmpId AND DOJ>=EmpJoining.DOJ))
ELSE 1 END)

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-07 : 07:16:33

Much simpler...

UPDATE	a
SET	a.DepartmentCode =	(
					SELECT TOP(1)	e.DepartmentCode
					FROM		dbo.EmpJoining AS e
					WHERE		e.EmpId = a.EmpId
							AND e.DOJ <= a.AttendanceDate
					ORDER BY	e.DOJ DESC
				)
FROM	dbo.Attendance AS a

N 56°04'39.26"
E 12°55'05.63"

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-07-07 : 07:49:44

Thank you so much peso and sql programmers.

Really much helpful. Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources