Inventory Counts in SQL

Question

I have a question about calculating sock out days using T-SQL in SQL Server 2008R2. So essentially I need a table that has an item, the date that the item was exhausted from inventory, and the date the item was replenished. The original table that I am working from is similar to this one

Item_Number | Inv_date_Change | QTY | Inventory_Change_Count
------------|-----------------|-----|-----------------------
    A1      |  2014-01-10     | 10  |  1
    A1      |  2014-01-09     | 0   |  2
    A1      |  2014-01-05     | -1  |  3
    A1      |  2014-01-03     | 10  |  4
    A1      |  2014-01-01     | 0   |  5
    B2      |  2014-01-10     | 5   |  1
    B2      |  2014-01-09     | 0   |  2
    B2      |  2014-01-05     | 1   |  2

Please note that the Inv_date_Change column is the date that the inventory changed for that item. I have added the Inventory_Change_Count column to be a counter of the amount of changes to the item inventory.

Also note that an items stock can change even though the quantity is exhausted (0 or less than 0)

The final product of what I am looking for is something like this:

Item_Number | Date_Exhausted | Date_Replenished
------------|----------------|-----------------
     A1     |   2014-01-05   |  2014-01-10     
     A1     |   2014-01-01   |  2014-01-03     
     B2     |   2014-01-09   |  2014-01-10     

I have tried working with a query similar to this, by joining that table back in on itself using the Inventory_Change_Count column as a way to select when the item was exhausted:

SELECT *
FROM Inventory a
LEFT JOIN Inventory b ON a.ITEMNMBR = b.ITEMNMBR AND a.LOCNCODE = b.LOCNCODE
        AND ((a.DTE_OUT = b.DTE_OUT - 1) 
                AND a.QTY > 0 
                AND b.QTY < 1)
        WHERE b.QTY IS NOT NULL

The issue I am running into with this query is that the first instance of item A1 was exhausted on 2014-01-05, not 2014-01-09 like this query will return.

I was also toying around with the idea of adding in the logic to look at when the next Inventory_Change_Count up is positive, then join on Inventory_Change_Count - 1 (The first appearance of a negative in the row). So something like:

join on where a.QTY = when b.qty >= 1 and b.Inventory_Change_Count > a.Inventory_Change_Count then a.Inventory_Change_Count - 1 o

But I don't know the exact SQL syntax to do this.

Does this make sense? Any ideas?

Thanks for all help in advance!

Answer 1

This is done with a relatively simple self-join. The problem is the "noise" in the raw data -- any rows with an exhausted quantity preceded immediately by another row also with an exhausted quantity (in order of time, of course). So step one it to provide a result set without those rows.

Then the self-join of the clean data. On side A, you will have all rows where the qty is <= 0. On side B, you need the row with positive qty and the most recent date after the date of side A. Easy.

with
Noise( Item_Number, Inv_date_Change, QTY )as(
    -- Select which rows are "noise." Noise is an exhausted row
    -- immediately preceded by another exhausted row.
    select  a.*
    from    Inventory a
    join    Inventory b
        on  b.Item_Number = a.Item_Number
        and b.Inv_date_Change =(
            select  Max( Inv_date_Change )
            from    Inventory
            where   Item_Number = a.Item_Number
                and Inv_date_Change < a.Inv_date_Change )
    where   a.qty <= 0
        and b.qty <= 0
),
Clean( Item_Number, Inv_date_Change, QTY )as(
    -- Now provide the noise-free data.
    select  r.*
    from    Inventory     r
    left join Noise n
        on  n.Item_Number = r.Item_Number
        and n.Inv_date_Change = r.Inv_date_Change
    where   n.Item_Number is null
)
select  a.Item_Number,
        a.Inv_date_Change as Date_Exhausted,
        b.Inv_date_Change as Date_Replenished
from    Clean a
join    Clean b
    on  b.Item_Number = a.Item_Number  -- Has to be the same item
    and b.Inv_date_Change =(           -- with...
        select  Min( Inv_date_Change ) -- most recent date
        from    Clean
        where   Item_Number = a.Item_Number
            and Qty > 0                               -- with non-zero quantity
            and Inv_date_Change > a.Inv_date_Change ) -- after exhaustion date
where   a.qty <= 0  -- And this is exhaustion
order by a.Item_Number, a.Inv_date_Change desc;

The question I then asked was what about an item that has been exhausted but not yet replenished. As you can see in my Fiddle, I have added a data line to represent just that situation. This is handled by just changing the final join to a left join. Thus, a null in the Date_Replenished field means that the inventory of that item remains exhausted at present.

Answer 2

select a.Item_number,a.Inv_date_Change,b.Inv_date_Change 
from Inventory a join Inventory b on a.Item_number = b.Item_Number and b.Inv_date_Change > a.Inv_date_Change 
where a.QTY <= 0 and b.QTY > 0
and not exists(select * from Inventory d where d.QTY > 0 and d.Item_Number = a.Item_Number and d.Inv_date_Change > a.Inv_date_Change and d.Inv_date_Change < b.Inv_date_Change)
and isnull((select top 1 d.QTY from Inventory d where d.Item_Number = a.Item_Number and d.Inv_date_Change < a.Inv_date_Change order by d.Inv_date_Change desc), 1) > 0