Table with "starting date" for data

[fredinand]

Hi all.

Suppose we have a table PLAYER_TEAM which stores the team (TEAM_ID) a player (PLAYER_ID) plays for, starting from a given date (STARTING_DATE). So we have something like this:

PLAYER_ID   TEAM_ID    STARTING_DATE
---------   -------    -------------
   1           1        20/10/2008
   1           3        11/12/2008
   1           2        08/01/2009
   1           1        25/03/2009

Player 1 plays for team 1 between 20/10/2008 and 10/12/2008, for team 3 between 11/12/2008 and 07/01/2009, for team 2 between 08/01/2009 and 24/03/2009, and again for team 1 from 25/03/2009.

I can make a query that, given a particular date and player, determine the team he was playing for.

But i'd like to have a query to make a view with the relation between players, teams and starting and finishing date.

PLAYER_ID   TEAM_ID    STARTING_DATE    ENDING_DATE
---------   -------    -------------    -----------
   1           1        20/10/2008       11/12/2008
   1           3        11/12/2008       08/01/2009
   1           2        08/01/2009       25/03/2009
   1           1        25/03/2009         (null)

I thought it would be easier, but i couldn't find a way.

I would add the ending_date column to the table, but it requires a more difficult management (when inserting and specially modifying dates).

Thanks in advance.
Fred.

 

[fredinand]

Well, i think i have it. It was easier than i thought.

select a.player_id, a.team_id, a.starting_date, min(b.starting_date)
from player_team a
left outer join player_team b
on a.player_id = b.player_id
and b.starting_date > a.starting_date
group by a.player_id, a.team_id, a.starting_date
order by a.player_id, a.starting_date

Thanks anyway.