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?
Assuming an item moves between some type of state or location (
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
_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
LAG() function) and the current
route_date_time. Bear in mind the
PARTITION BY clause, which defines how you partition your records.