Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2012
    Posts
    16

    Unanswered: Finding TimeOut from next records

    Create table t1 (traceid number, objectid number, locationid number, time number)

    insertinto t1 values(1,1,1,1)
    insertinto t1 values(2,1,1,2)
    insertinto t1 values(3,1,2,4)
    insertinto t1 values(4,1,2,6)
    insertinto t1 values(5,1,3,9)
    insertinto t1 values(6,2,1,3)
    insertinto t1 values(7,2,1,5)
    insertinto t1 values(8,2,3,8)

    The timein of an object at a location is min(time) of the object at the location and timeout is min(time) of the object in next location.

    I need sql which can fetch the records as the following:

    (ObjectID, FromLocationId,ToLocationID, timein, timeout)

    (1, 1, 2, 1, 4)
    (1, 2, 3, 4, 9)
    (2, 1, 3, 3, 8)

  2. #2
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Lightbulb

    You need to make use of window functions (LEAD and MIN).

    This should do it:
    Code:
    select objectid, 
           locationid fromlocationid, nextlocationid tolocationid,
           timein, nexttime timeout
      from (
      select objectid, 
             locationid, 
             lead(locationid) over (partition by objectid 
                                        order by locationid, time) nextlocationid,
             min(time)  over (partition by objectid, locationid) timein,
             lead(time) over (partition by objectid
                                  order by locationid, time) nexttime
        from t1) v
    where v.locationid != v.nextlocationid -- the location has changed
      and v.nextlocationid is not null     -- exclude the current location
    order by objectid, locationid
    /
    Here's the output from SQL*PLUS:
    Code:
    dayneo@RMSD> edit
    Wrote file afiedt.buf
    
      1  select objectid,
      2         locationid fromlocationid, nextlocationid tolocationid,
      3         timein, nexttime timeout
      4    from (
      5    select objectid,
      6           locationid,
      7           lead(locationid) over (partition by objectid
      8                                      order by locationid, time) nextlocation
    id,
      9           min(time)  over (partition by objectid, locationid) timein,
     10           lead(time) over (partition by objectid
     11                                order by locationid, time) nexttime
     12      from t1) v
     13  where v.locationid != v.nextlocationid -- the location has changed
     14    and v.nextlocationid is not null     -- exclude the current location
     15* order by objectid, locationid
    dayneo@RMSD> /
    
      OBJECTID FROMLOCATIONID TOLOCATIONID     TIMEIN    TIMEOUT
    ---------- -------------- ------------ ---------- ----------
             1              1            2          1          4
             1              2            3          4          9
             2              1            3          3          8
    
    dayneo@RMSD>
    PS. Thanks for the sample data!

  3. #3
    Join Date
    Jul 2012
    Posts
    16
    Oops it's dam hard.
    Thanks for your too complicated solution. :-)
    I already wrote c# code while waiting for the solution.
    I learn new terms "LEAD" from your post. Which will help me in future.

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    If it can be done in SQL, then do it in SQL. It will be faster than loop..loop..loop.
    But I just thought of more scenarios...
    What happens of object 1 moves from location 3 back to location 2?

  5. #5
    Join Date
    Jul 2012
    Posts
    16
    Yes I tried several ways using sql to do this. But it produced some extra results.
    Like:
    object:1 from:1 to: 3 ..... --Even object 1 is not moving from 1 to 3 directly

    In my case object should not come back from 3 to 2.
    Even if it comes back then new records should be there. Like
    object:1 from :3 to:2 timein:X timeout:Y.

    In my code I sort them in ascending order based on time.
    Then for each object I checked whither it changed it's location or not. If it changes its location then I add a new record.

    Thanks.

  6. #6
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Lightbulb

    I know that you have solved this problem through front end C# loop code, but out of interest I worked out how to do this in SQL.

    The SQL now caters for the fact that the object can:
    • Move sequentially from one location to another, or
    • Skip locations, or
    • Move back and forth between locations
    • No longer requires locationid's to be numeric.

    Code:
    select objectid,
           prevlocationid fromlocationid,
           locationid tolocationid,
           prevtime timein,
           time timeout
      from (select objectid,
                   prevlocationid,
                   locationid,
                   lag(time) over (partition by objectid order by time) prevtime,
                   time
              from (select objectid,
                           lag(locationid) over (partition by objectid 
                                                     order by time) prevlocationid, 
                           locationid, 
                           TIME
                      from t1)
             where prevlocationid != locationid
                or prevlocationid is null)
     where prevlocationid is not null
    order by objectid, time

  7. #7
    Join Date
    Jul 2012
    Posts
    16
    Thanks :-).
    I feel I need to learn about lag, lead and over function.

Posting Permissions

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