none
Queries - Referencing Last Entry RRS feed

  • Question

  • Hello!

    Pardon my newness as I am a first-time user of the forum and relatively inexperienced SQL user.  Hopefully I posted in the right place.

    I'm trying to design a query that can reference back to the most recent entry before a current entry for several hundred thousand observations.

    The data captures the location of our customers' equipment.  So location might be "Atlanta, GA".  Each entry also comes with a timestamp of when it was updated.

    Imagine a piece of equipment that moved from Atlanta, GA to Louisville, KY.  I'd like the row of the output to show the "current" location and date and the "last" location and status and date, calculate the time between dates, and flag whether location changed or not.  So it might read...

    Current Date | Current Location |  Last Date  | Last Location |  Time Transpired |  Location Changed  

    3/1/2018       |  Louisville, KY    |  2/29/2018| Atlanta, GA    |   2 [days]           |  Yes

    The first two columns are easy for me, but I have no idea how to do the second 2 (Last Date and Last Location).  The first thing that comes to mind is Common Table Expression, but I'm not sure how to get it to pull the MOST RECENT entry before the current one.  In Excel I would normally use a MAXIFS function on the date and then match to that entry - but I'm not sure what the SQL equivalent is.

    I'm also not sure how to execute the final 2 columns - Time Transpired and Location Changed.  These are simple logic formulas but they need to reference back to the first four columns.  Rather than repeating whatever I do for the middle 2 columns, can I label each of the first four columns as a "new" field to easily reference back to them with these logic functions?

    Thanks for any help you can provide!  Not trying to get someone to do my work (i.e. write the query) for me but would be awesome if you can point me in the right direction as far as what functions to use.

    Thursday, October 25, 2018 9:08 PM