CODEWITHSUNDEEP
sql-serversql-server-2012
leejkennedy
leejkennedy

Reputation: 5

return each date that a value changed

I have a table with a PersonID, TeamID, and DateEffective. I'd like to return the earliest date that each person changed team. It is possible that a person can move back into a team they were previously allocated to.

Create Table PHistory
    (PersonID INT,
    TeamID VARCHAR(8),
    DateEffective datetime)

Insert INTO PHistory
Values (1, 'TeamA', '2017-07-01'),
       (1, 'TeamA', '2017-07-02'),
       (1, 'TeamB', '2017-07-03'),
       (1, 'TeamA', '2017-07-04'),
       (2, 'TeamA', '2017-07-01'),
       (2, 'TeamA', '2017-07-02'),
       (2, 'TeamA', '2017-07-03'),
       (2, 'TeamA', '2017-07-04')

I'd like the query to return:

I've messed around with MIN() but it doesn't cope with the fact that people can move back into old teams.

I've seen this post that uses a window function, but it only returns the latest record and I need to see all team changes (I couldn't see how to adapt it to my needs).

Upvotes: 0

Views: 24

Answers (1)

uzi
uzi

Reputation: 4146

Here is one approach using row_number

select
    PersonId, minDate = min(DateEffective)
from (
    select 
        *, rn1 = row_number() over (partition by PersonId order by DateEffective)
        , rn2 = row_number() over (partition by PersonId, TeamId order by DateEffective)
    from 
        PHistory
) t
group by PersonId, rn1 - rn2
order by 1,2

Output

1   2017-07-01 00:00:00.000
1   2017-07-03 00:00:00.000
1   2017-07-04 00:00:00.000
2   2017-07-01 00:00:00.000

Upvotes: 1

Related Questions