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