Return the most recent event in a series of same title events

Question

I need to be able to return the most recent event of a occurrence that may or may not have occurred more than once. For instance:

Each time a product is moved from position A to Position B, it is recorded (From_Function) / (To_Function) along with its operator and timestamp. On rare occasions, re-work is required, and that unit will show the same from / To instance with different timestamps.

I am currently calculating age in each position with a datediff, how do I return only the most recent in this scenario?

I got it down to if there is no group by clause it will return the date needed with a max(route_date_time); however, the total qry is many lines long with aggregate functions so a group by is necessary. How can I return only the one line that is needed with the most recent date?

score 1 · Answer 1 · answered Jan 16 '16 at 09:36

Assuming an item moves between some type of state or location (From_Function, To_Function) and the timestamp of the move is in the route_date_time column, you could put your query in a common table expression, along with a ROW_NUMBER() function that calculates a sequence number, like so:

WITH cte AS (
    SELECT item_no,
           ToFunction AS current_function,
           route_date_time,
           ROW_NUMBER() OVER (
               PARTITION BY item_no
               ORDER BY route_date_time DESC) AS _rownum
    FROM some_complex_query
)

SELECT *
FROM cte
WHERE _rownum=1;

Because we've partitioned the ROW_NUMBER() function on item_no and ordered it by route_date_time descending, _rownum=1 will always return the most recent row for each item_no, which is what we're isolating using the WHERE clause at the end of the query.

If you need the time that each item has spent in a state, you could use LAG() (requires SQL Server 2012 or newer). Within the CTE, use the following expression:

    DATEDIFF(second,
             LAG(route_date_time, 1) OVER (
                 PARTITION BY item_no
                 ORDER BY route_date_time),
             route_date_time
            ) AS time_spent_in_function

This will return the difference in number of seconds between the previous route_date_time (the LAG() function) and the current route_date_time. Bear in mind the PARTITION BY clause, which defines how you partition your records.

Return the most recent event in a series of same title events

1 Answers1