INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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 :

CODE

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 

CODE

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 :

CODE

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 :

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close