Return the most recent event in a series of same title events
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.