Re: SQL statement help please

From: Tokunaga T. <tonkuma_at_jp.ibm.com>
Date: 2 Aug 2003 19:19:33 -0700
Message-ID: <8156d9ae.0308021819.48a665c0_at_posting.google.com>

> 
> I am using MySQL so could you guru give me any idea of the ANSI SQL rather
> than MS SQL Server specific, thanks !

This worked in DB2 UDB.
CREATE TABLE Vehicles
(VehicleID SMALLINT NOT NULL
,Time TIME NOT NULL
,Station CHAR(8) NOT NULL

);  

INSERT INTO Vehicles
VALUES

 (1 , '09:00:00' , 'A')


,(1 , '09:10:00' , 'B')

,(1 , '09:20:00' , 'C')

,(1 , '09:30:00' , 'A')

,(1 , '09:40:00' , 'B')

,(1 , '09:50:00' , 'C')

,(1 , '10:00:00' , 'D')
,(1 , '10:10:00' , 'E')

,(2 , '09:10:00' , 'A')
,(2 , '09:20:00' , 'B')
,(2 , '09:40:00' , 'C')
,(2 , '09:50:00' , 'D')
,(2 , '10:10:00' , 'A')
,(2 , '10:20:00' , 'B')
,(2 , '10:30:00' , 'A')
,(2 , '10:40:00' , 'B')
,(2 , '10:50:00' , 'C')
,(2 , '11:00:00' , 'D')
,(2 , '12:10:00' , 'E')

;  

SELECT A.VehicleID

     , SUBSTR(CHAR(A.Time), 1, 5) A
     , SUBSTR(CHAR(B.Time), 1, 5) B
     , SUBSTR(CHAR(C.Time), 1, 5) C
     , SUBSTR(CHAR(D.Time), 1, 5) D
     , SUBSTR(CHAR(E.Time), 1, 5) E
  FROM Vehicles A
       LEFT OUTER JOIN
       Vehicles A2
          ON  A2.Station = 'A'
          AND A2.VehicleID = A.VehicleID
          AND A2.Time > A.Time
       LEFT OUTER JOIN
       Vehicles B
          ON  B .Station = 'B'
          AND B .VehicleID = A.VehicleID
          AND B.Time > A.Time AND (B.Time < A2.Time OR A2.Time IS NULL)
       LEFT OUTER JOIN
       Vehicles C
          ON  C .Station = 'C'
          AND C .VehicleID = A.VehicleID
          AND C.Time > A.Time AND (C.Time < A2.Time OR A2.Time IS NULL)
       LEFT OUTER JOIN
       Vehicles D
          ON  D .Station = 'D'
          AND D .VehicleID = A.VehicleID
          AND D.Time > A.Time AND (D.Time < A2.Time OR A2.Time IS NULL)
       LEFT OUTER JOIN
       Vehicles E
          ON  E .Station = 'E'
          AND E .VehicleID = A.VehicleID
          AND E.Time > A.Time AND (E.Time < A2.Time OR A2.Time IS NULL)

 WHERE A .Station = 'A'
   AND

     (
       A2.Time = (SELECT MIN(Time)
                    FROM Vehicles Am
                   WHERE Am.Station = 'A'
                     AND Am.VehicleID = A.VehicleID
                     AND Am.Time > A.Time
                 )
      OR
       A2.Time IS NULL
     )

ORDER BY 1, 2
;

---

 

VEHICLEID A B C D E --------- ----- ----- ----- ----- -----

        1 09:00 09:10 09:20 -     -     
        1 09:30 09:40 09:50 10:00 10:10 
        2 09:10 09:20 09:40 09:50 -     
        2 10:10 10:20 -     -     -     
        2 10:30 10:40 10:50 11:00 12:10 

  5 record(s) selected. Received on Sun Aug 03 2003 - 04:19:33 CEST