Show Last record of Employee a/c to EndDate

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

SQL Server 2005 Forums

Transact-SQL (2005)

Show Last record of Employee a/c to EndDate

Author

Topic

qasimidl
Starting Member

10 Posts

Posted - 2012-07-04 : 09:10:45

i have some tables

name employeeshifts
Shifts 1= A,2=B,3=C

columns are
sno Shifts EmployeeID Startdate EndDate ChangedDate
1 1 1 2012-12-12 00:00:00.000 2012-12-15 00:00:00.000 2012-12-13 00:00:00.000

2 2 1 2012-12-13 00:00:00.000 2012-12-18 00:00:00.000 null

3 3 1 2012-12-19 00:00:00.000 2012-12-21 00:00:00.000 null

4 1 2 2012-12-12 00:00:00.000 2012-12-15 00:00:00.000 null

5 2 4 2012-12-12 00:00:00.000 2012-12-15 00:00:00.000 null

6 3 5 2012-12-12 00:00:00.000 2012-12-15 00:00:00.000 null

7 1 9 2012-12-16 00:00:00.000 2012-12-18 00:00:00.000 2012-12-16 00:00:00.000

8 2 9 2012-07-07 00:00:00.000 2012-08-17 00:00:00.000 null

there is employee table as well

what i need to show
last record of employee where employee has performed or is performing duty

suppose
employee 1 has performed his last duty a/c to above table
2012-12-21 00:00:00.000 in C shifts
Employee 9 will perform will be performing duty
2012-08-17 00:00:00.000 in B shifts

Just like this i need to show last record where employee performs duty

thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-04 : 10:00:32

You can use row_number function like this:

SELECT
	s.EmployeeId,
	e.ShiftName,
	s.EndDate
FROM
(
SELECT
	*,
	ROW_NUMBER() OVER (PARTITION BY EmployeeId ORDER BY EndDate DESC) AS RN
FROM
	YourShiftsTable
) s
INNER JOIN EmployeeShifts e ON e.ShiftId = s.Shifts
WHERE RN = 1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-04 : 19:03:39

see scenario 2

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources