CODEWITHSUNDEEP
sqlinventory
Alesito
Alesito

Reputation: 605

SQL inventory with history

Well, I have a problem with creating a query to retrieve current inventory from a table which contains data in this way: ID, Warehouse, Date, Stock

What I would like to get is the current stock information which is dated by last change of stock.

For example: 

1, A, 2013-01-01, 1  
1, A, 2013-01-02, 2  
1, A, 2013-01-03, 4  
1, A, 2013-01-05, 1  
1, B, 2013-01-01, 1  
1, B, 2013-01-02, 2  
1, B, 2013-01-03, 4  
2, B, 2013-01-01, 1  
2, B, 2013-01-02, 2  
2, B, 2013-01-03, 4

And as result I would like:

1, A, 2013-01-05, 1  
1, B, 2013-01-03, 4  
2, B, 2013-01-03, 4

For each ID last stock info for each warehouse.

Upvotes: 0

Views: 505

Answers (2)

Jack Jiang
Jack Jiang

Reputation: 522

SELECT t1.ID, t1.Warehouse, t1.Date, Stock
FROM TestTable t1
JOIN
(
    SELECT ID, Warehouse, MAX(Date) [Date]
    FROM TestTable
    GROUP BY ID, Warehouse
) t2 ON t1.ID = t2.ID
    AND t1.Warehouse = t2.Warehouse
    AND t1.Date = t2.Date
ORDER BY ID, Warehouse

Upvotes: 0

oerkelens
oerkelens

Reputation: 5161

This version still works if you have or will get other columns as well, without having to add new lines with TOP, sorting etc :)

select *
from TableName t1
where t1.Date = 
      (select max(t2.Date) 
         from TableName t2
        where t2.Id = t1.id and t2.Warehouse = t1.WareHouse)

Upvotes: 1

Related Questions