History of Available Stock

[Former Member]

Hello,

How can I find what was the available stock on a specific date in the past?

I know how to get OnHand for any date in the past:

SELECT LocType, LocCode, ItemCode, SUM(InQty-OutQty) [OnHand]
From OIVL
Where DocDate <= '7/7/2017'
   AND ITEMCODE = 'xyz' 
Group by LocType, LocCode, ItemCode

But this does not account for what was committed. It shows what was on hand, but not what was actually available (OnHand - Committed = Available). So, how could I get to this "Available" number for dates in the past?

Thanks!

[Former Member · Accepted solution]

For anyone else who might need this. This will give you historical stock quantities and cost. Just pass in the item code and the date you want to check. If you omit item code it returns detail for all items.

CREATE FUNCTION [dbo].[ufnStockAndCostHistory]
(    
    @EndDate DATETIME, @ItemCode NVARCHAR(255) = 'All'
)
RETURNS TABLE 
AS
RETURN 
(
    WITH Cost AS (
        SELECT
            OINM.TransNum,
            OINM.ItemCode,
            OINM.Warehouse,
            OINM.Balance,
            OINM.CreateDate
        FROM OINM (NOLOCK)
            JOIN OITM (NOLOCK) ON OITM.ItemCode = OINM.ItemCode
            JOIN OITB (NOLOCK) ON OITM.ItmsGrpCod=OITB.ItmsGrpCod
        WHERE  OINM.CreateDate <= @EndDate

    ),
    FinalCostByWarehouse AS (
        SELECT *
        FROM Cost 
        WHERE TransNum = (
            SELECT MAX(TransNum)
            FROM Cost sub
            WHERE Cost.ItemCode = sub.ItemCode
                AND Cost.Warehouse = sub.Warehouse)
    ),
    Quantity AS (
        SELECT
            OINM.ItemCode,
            OINM.Warehouse,
            SUM(OINM.InQty) - SUM(OINM.OutQty) [Qty]
        FROM OINM (NOLOCK)
            JOIN OITM (NOLOCK) ON OITM.ItemCode = OINM.ItemCode
            JOIN OITB (NOLOCK) ON OITM.ItmsGrpCod=OITB.ItmsGrpCod
        WHERE OINM.CreateDate <= @EndDate
        group by OINM.ItemCode, OINM.Warehouse
    )
    SELECT 
        OITW.ItemCode,
        OITW.WhsCode,
        c.Balance [Cost],
        q.Qty [Quantity]
    FROM OITW
        LEFT JOIN FinalCostByWarehouse c ON OITW.ItemCode = c.ItemCode
            AND OITW.WhsCode = c.Warehouse
        LEFT JOIN Quantity q ON OITW.ItemCode = q.ItemCode
            AND OITW.WhsCode = q.Warehouse
    WHERE (OITW.ItemCode = @ItemCode
        OR @ItemCode = 'All')
        AND (ISNULL(c.Balance,0) > 0
            OR ISNULL(q.Qty,0) > 0)
)

Usage looks like:

SELECT *
FROM ufnStockAndCostHistory('9/15/2017','B107A-12HB')

Thanks,

Ian