How to get the last plus and last minus datetime from a stock table

February 20, 2024

I have an SQL table with the following structure:

CREATE DATABASE TestDB;
GO
USE TestDB;
CREATE TABLE dbo.TestForStock
( ID INT IDENTITy(1,1)
,WarehouseID BIGINT
,ItemID BIGINT
,Qty INT
,DatetimeStamp datetime
,Remarks NVARCHAR(100)
)

INSERT INTO dbo.TestForStock
SELECT 20,392,100, '2023-06-28 16:57:43.143','Item Added' UNION ALL
SELECT 20,392,-1, '2023-06-29 15:54:41.997','Item removed' UNION ALL
SELECT 20,392,-1, '2023-06-29 15:54:42.037','Item removed' UNION ALL
SELECT 20,128,50, '2023-06-20 17:38:43.030','Item Added' UNION ALL
SELECT 20,128,-1, '2023-06-21 17:38:43.030','Item removed' UNION ALL
SELECT 20,128,-1, '2023-06-22 17:38:43.030','Item removed' UNION ALL
SELECT 20,128,10, '2023-06-25 17:38:43.030','Item Added' UNION ALL
SELECT 20,128,-1, '2023-06-26 17:38:43.030','Item removed' UNION ALL
SELECT 20,128,-1, '2023-06-27 17:38:43.030','Item removed'

This is a warehouse item stock table having entries of each transaction when item was added or removed from a warehouse.

I want to get the age of items in their warehouses, so for that I want to get the latest date when any item was added in any warehouse and the latest date when it was moved from the warehouse(means date of the latest plus and minus qty field).

How can I do it, I tried doing it via the following sql, but its way too slow and not efficient and it should be done in a single query:

SELECT TOP 1
    W.Code AS WarehouseCode,
    W.Name AS WarehouseName,
    I.Code AS SKU,
    I.Name AS ItemName,
    CONVERT(VARCHAR(20), WS.DateTimeStamp ,106) AS LastPlusDatetime
FROM WareHouse W WITH(NOLOCK)
INNER JOIN WareHouseStock WS WITH(NOLOCK) ON WS.WareHouseID = W.ID
INNER JOIN Item I WITH(NOLOCK) ON WS.ItemID = I.ID
WHERE W.Code = @WarehouseCode
AND I.Code = @SKU
AND Qty > 0
ORDER BY WS.ID DESC

SELECT TOP 1
    W.Code AS WarehouseCode,
    W.Name AS WarehouseName,
    I.Code AS SKU,
    I.Name AS ItemName,
    CONVERT(VARCHAR(20), WS.DateTimeStamp ,106) AS LastMinusDatetime
FROM WareHouse W WITH(NOLOCK)
INNER JOIN WareHouseStock WS WITH(NOLOCK) ON WS.WareHouseID = W.ID
INNER JOIN Item I WITH(NOLOCK) ON WS.ItemID = I.ID
WHERE W.Code = @WarehouseCode
AND I.Code = @SKU
AND Qty < 0
ORDER BY WS.ID DESC

For the provided schema example, the expected result should be:

20 392 100 ‘2023-06-28 16:57:43.143’ AS LatestPlusDate ‘2023-06-29 15:54:42.037’ AS LatestMinusDate

20 128 10 ‘2023-06-25 17:38:43.030’ AS LatestPlusDate ‘2023-06-27 17:38:43.030’ AS LatestMinusDate

>Solution :

Something like this perhaps?

CREATE TABLE dbo.TestForStock
( ID INT IDENTITy(1,1)
,WarehouseID BIGINT
,ItemID BIGINT
,Qty INT
,DatetimeStamp datetime
,Remarks NVARCHAR(100)
)

INSERT INTO dbo.TestForStock
SELECT 20,392,100, '2023-06-28 16:57:43.143','Item Added' UNION ALL
SELECT 20,392,-1, '2023-06-29 15:54:41.997','Item removed' UNION ALL
SELECT 20,392,-1, '2023-06-29 15:54:42.037','Item removed' UNION ALL
SELECT 20,128,50, '2023-06-20 17:38:43.030','Item Added' UNION ALL
SELECT 20,128,-1, '2023-06-21 17:38:43.030','Item removed' UNION ALL
SELECT 20,128,-1, '2023-06-22 17:38:43.030','Item removed' UNION ALL
SELECT 20,128,10, '2023-06-25 17:38:43.030','Item Added' UNION ALL
SELECT 20,128,-1, '2023-06-26 17:38:43.030','Item removed' UNION ALL
SELECT 20,128,-1, '2023-06-27 17:38:43.030','Item removed' 

select warehouseid, itemid
,   max(case when qty > 0 then datetimestamp end) as incoming
,   max(case when qty < 0 then datetimestamp end) as outgoing
from TestForStock
group by warehouseid, ItemID

max(case when qty < 0 then datetimestamp end) is a technique called conditional aggregation, where you get the value you want by using a CASE WHEN condition which filters out what you want.