Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2013
    Posts
    3

    Unanswered: How to select second row column data in first row and to find diff between two column

    Hi I have a requirement like below can any one help for me.
    input table will be like below:
    eventdata:
    accountID deviceID timestamp speedKPH address
    --------- -------- ---------- -------- -------------
    preva1 bolero 1359089006 15 Ullalu Road
    preva1 bolero 1359088796 0 Ullalu Road
    preva1 bolero 1359088886 0 Ullalu Road
    preva1 bolero 1359088888 8.47 Ullalu Road
    preva1 bolero 1359088986 0 Ullalu Road
    preva1 bolero 1359088988 45 Ullalu Road
    preva1 bolero 1359088996 21 Ullalu Road
    preva1 bolero 1359088998 0 Ullalu Road
    preva1 bolero 1359089006 15 Ullalu Road
    preva1 bolero 1359089009 12 Ullalu Road
    preva1 bolero 1359089006 15 Ullalu Road
    preva1 bolero 1359089016 0 Ullalu Road
    preva1 bolero 1359089026 0 Ullalu Road
    So here i need output table like below:
    stoppagedetails:
    accountID deviceID from_timestamp to_timestamp diff
    --------- -------- ---------- -------- -------------
    preva1 bolero 1359088796 1359088888 92
    preva1 bolero 1359088986 1359088988 2
    preva1 bolero 1359088998 1359089006 8
    preva1 bolero 1359089016

    So can any one help how to write mysql query for the above requirement.Thanks in advance..

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Please explain your requirements
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2013
    Posts
    3
    Quote Originally Posted by gvee View Post
    Please explain your requirements
    Hi,
    Actually i tracking data for vehicle which is moving So in that i will get all the data related to vehicle i.e some times it is moving with some speed and some point near to signal it may be stopped for 2 sec like that So here i need to find time difference and display
    That only i send above
    accountID deviceID timestamp speedKPH address
    --------- -------- ---------- -------- -------------
    preva1 bolero 1359089006 15 Ullalu Road
    preva1 bolero 1359088796 0 Ullalu Road---}it is stopped
    preva1 bolero 1359088886 0 Ullalu Road----}it is stopped
    preva1 bolero 1359088888 8.47 Ullalu Road
    preva1 bolero 1359088986 0 Ullalu Road
    ..
    ...
    So i need output table for above like below
    accountID deviceID from_timestamp to_timestamp diff
    --------- -------- ---------- -------- -------------
    preva1 bolero 1359088796 1359088888 92
    ...
    ...continue soon
    Last edited by Harish@dts; 11-19-13 at 07:35.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The example was not tested on MySQL.
    (Tested on DB2.)
    So, you might want to make an amendment on MySQL.

    Example:
    Code:
    SELECT accountID
         , deviceID
         , MIN(fr_timestamp) AS from_timestamp
         , to_timestamp
         , to_timestamp
         - MIN(fr_timestamp) AS diff
     FROM  (SELECT fr.accountID
                 , fr.deviceID
                 , fr.timestamp AS fr_timestamp
                 , (SELECT MIN(to.timestamp)
                     FROM  eventdata AS to
                     WHERE to.accountID = fr.accountID
                       AND to.deviceID  = fr.deviceID
                       AND to.timestamp > fr.timestamp
                       AND to.speedKPH  > 0
                   ) AS to_timestamp
             FROM  eventdata AS fr
             WHERE fr.speedKPH = 0
           ) AS s
     GROUP BY
           accountID
         , deviceID
         , to_timestamp
    ;

  5. #5
    Join Date
    Nov 2013
    Posts
    3
    Thanks It is working fine..




    Quote Originally Posted by tonkuma View Post
    The example was not tested on MySQL.
    (Tested on DB2.)
    So, you might want to make an amendment on MySQL.

    Example:
    Code:
    SELECT accountID
         , deviceID
         , MIN(fr_timestamp) AS from_timestamp
         , to_timestamp
         , to_timestamp
         - MIN(fr_timestamp) AS diff
     FROM  (SELECT fr.accountID
                 , fr.deviceID
                 , fr.timestamp AS fr_timestamp
                 , (SELECT MIN(to.timestamp)
                     FROM  eventdata AS to
                     WHERE to.accountID = fr.accountID
                       AND to.deviceID  = fr.deviceID
                       AND to.timestamp > fr.timestamp
                       AND to.speedKPH  > 0
                   ) AS to_timestamp
             FROM  eventdata AS fr
             WHERE fr.speedKPH = 0
           ) AS s
     GROUP BY
           accountID
         , deviceID
         , to_timestamp
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •