SQLTeam.com | Weblogs | Forums

Find minimum date after a change


#1

I have a problem I'm sure one of you guys will have a simple solution.

I have a range of data relating to structure of staff, so for example:
Staff ID, department code, team from, team to

Sometime staff will move from one department to another and then back again and what I need to work out is the minimum team from for the last move related to that department.

So as an example:

55555, T, 01/09/2015 30/09/2015
55555, H, 01/07/2015, 31/08/2015,
55555, T, 01/01/2013, 30/06/2015

so what I would want to see as an output would be

55555, T, 01/09/2015, 30/09/2015
55555, H, 01/07/2015, 31/08/2015

Any help please?


#2
SELECT *
FROM
(
    select *, row_number() over (partition by [department code] 
                                     order by [team to] desc)
    from   yourtable
) d
WHERE d.rn = 1