SQL Server Query Last Moved Item

user_7983599 0
Viewed 42

We have a storage system that is full. I am trying to identify items currently in the storage system that are 'inactive' (have not been moved in a long time). With this I would have an easy way of identifying which items I can remove from the system to free up those locations for active items. I want a query that will show me the date of the last movement for each item (one result per unique item). Does that make sense?

I have 2 tables, that will need to be joined using item_id.

movement_log:

item_id     time                        operation   container   quantity
A           2020-01-01 11:00:00.000     Load        1           90
B           2020-01-01 13:00:00.000     Load        2           30
C           2020-01-02 09:00:00.000     Load        3           10
D           2020-01-03 15:00:00.000     Load        4           7
E           2020-01-03 15:30:00.000     Load        6           220
A           2020-01-03 16:00:00.000     Load        5           20
B           2020-01-03 17:00:00.000     Unload      2           10
C           2020-01-04 09:00:00.000     Load        3           5
D           2020-01-05 11:00:00.000     Unload      4           2
D           2020-01-06 11:00:00.000     Unload      4           2
B           2020-01-06 12:00:00.000     Unload      2           10
A           2020-01-06 13:00:00.000     Unload      1           10
E           2020-01-06 14:00:00.000     Unload      6           220

current_item_positions:

item_id     container   quantity
A           1           80
B           2           10
C           3           15
D           4           3
A           5           20
C           3           15

Query Result:

item_id     last_movement               last_operation
C           2020-01-04 09:00:00.000     Load
D           2020-01-06 11:00:00.000     Unload
B           2020-01-06 12:00:00.000     Unload
A           2020-01-06 13:00:00.000     Unload

Any ideas for how to achieve this?

greatest-n-per-group where-clause sql-server sql database
1 Answers

If I followed you correctly, you want the latest movement per item_id. If so, one option is to filter with a subquery:

select m.item_id, m.time as last_movement, m.operation as last_operation
from movement_log m
where m.time = (select max(m1.time) from movement_log m1 where m1.item_id = m.item_id)
order by last_movement

This query would take advantage of an index on movement_log(item_id, time).

I don't see the use of table current_item_positions here, as all the information you need is available in movement_log.

Another option is row_number():

select m.item_id, m.time as last_movement, m.operation as last_operation
from (
    select m.*, row_number() over(partition by item_id order by time desc) rn
    from movement_log m
) m
where rn = 1
user_106767160
Related