query question : get previous, current and next teams from 1 table

[FALCONSEYE]

I am trying to get a historical representation of the previous, current and next team information broken down by start and end dates. Here is the sql fiddle.

My output will look like :

name | previous team | previous team start date  | previous team end date | current team | current team start date | current team end date | next team | next team start date | next team end date

john doe | frankfurt | July, 01 2015 | August, 31 2015 | manchester | September, 01 2015 | null | null | null | null

john doe | dresden | December, 01 2014 | June, 30 2015 | frankfurt | July, 01 2015 | August, 31 2015 | manchester | September, 01 2015 | null

john doe | paris | September, 02 2014 | November, 30 2014 |  dresden | December, 01 2014 | June, 30 2015 | frankfurt | July, 01 2015 | August, 31 2015

This is what I have so far :

SELECT  DISTINCT  T1.name AS pname ,  T1.team AS current_team , T1.startDate AS current_team_start_date , T1.endDate AS current_team_end_date ,
      T2.pname ,  T2.previous_team , T2.previous_team_start_date , T2.previous_team_end_date   


FROM
   ( SELECT    * 
    FROM
    ( SELECT @row_num := IF( @prev_value = t.name,
                 @row_num + 1,
                 1  ) AS Rank,
    t.name, t.team, t.startDate, t.endDate ,             
        @prev_value := t.name 

      FROM  tblTeamMove t,

      ( SELECT @row_num := 1) M,

      ( SELECT  @prev_value := '') N

      ORDER BY t.name DESC, t.startDate DESC ) T 

WHERE T.Rank = 1 ) T1 

 LEFT OUTER JOIN tblTeamMove D  ON D.name = T1.name  
 LEFT OUTER JOIN (   SELECT  DISTINCT  T1.name AS pname ,  T1.team AS previous_team , T1.startDate AS previous_team_start_date , T1.endDate AS previous_team_end_date  
        FROM
          ( SELECT    * 
            FROM
                ( SELECT @row_num := IF( @prev_value = t.name,
                             @row_num + 1,
                             1  ) AS Rank,
                t.name, t.team, t.startDate, t.endDate ,             
                @prev_value := t.name 

                  FROM  tblTeamMove t,

                  ( SELECT @row_num := 1) M,

                  ( SELECT  @prev_value := '') N

                  ORDER BY t.name DESC, t.startDate DESC ) T 

            WHERE T.Rank = 2 ) T1 

        LEFT OUTER JOIN tblTeamMove D  ON D.name = T1.name 
        ORDER BY T1.name,   T1.team  ) T2 ON T2.pname = T1.name 
 ORDER BY T1.name,   T1.team

This is the script to create the table and some data :

CREATE TABLE `tblTeamMove` (

  `playerID` INT(11) NOT NULL,

  `name` VARCHAR(50) DEFAULT NULL,

  `team` VARCHAR(50) DEFAULT NULL,

  `startDate` DATE DEFAULT NULL,

  `endDate` DATE DEFAULT NULL,

  PRIMARY KEY (`playerID`)

);

INSERT INTO tblTeamMove ( playerID, NAME, team, startDate, endDate )

VALUES ( 1, 'john doe', 'manchester', '2015-09-01', NULL ) ;

 

INSERT INTO tblTeamMove ( playerID, NAME, team, startDate, endDate )

VALUES ( 2, 'john doe', 'frankfurt', '2015-07-01', '2015-08-31' ) ;

 

INSERT INTO tblTeamMove ( playerID, NAME, team, startDate, endDate )

VALUES ( 3, 'john doe', 'dresden', '2014-12-01', '2015-06-30' ) ;

 

INSERT INTO tblTeamMove ( playerID, NAME, team, startDate, endDate )

VALUES ( 4, 'john doe', 'paris', '2014-09-02', '2014-11-30' ) ;

 

INSERT INTO tblTeamMove ( playerID, NAME, team, startDate, endDate )

VALUES ( 5, 'john doe', 'zurich', '2014-08-18', '2014-09-01' ) ;

 

--

 

INSERT INTO tblTeamMove ( playerID, NAME, team, startDate, endDate )

VALUES ( 6, 'jane doe', 'lizbon', '2015-08-31', NULL ) ;

 

INSERT INTO tblTeamMove ( playerID, NAME, team, startDate, endDate )

VALUES ( 7, 'jane doe', 'madrid', '2015-05-01', '2015-08-30' ) ;

 

--

 

INSERT INTO tblTeamMove ( playerID, NAME, team, startDate, endDate )

VALUES ( 8, 'jim doe', 'budapest', '2015-07-01', '2015-08-25' ) ;

 

INSERT INTO tblTeamMove ( playerID, NAME, team, startDate, endDate )

VALUES ( 9, 'jim doe', 'budapest', '2014-12-01', '2015-05-31' ) ;

Thanks in advance

ColdFusion Ninja for hire.