SQL retrieve break in dates

[HeelNGville]

Preface this with thanks for any assistance in resolving what I hope is a relatively easy fix.

So, I have a table (sellable_prod_inv) with products (column_name: product_id) and inventory date (column_name: inventory_dt (mm/dd/yyyy), which contains a snapshot of inventory values by day, dating back many years. I am trying to return the oldest inventory date record, particularly in instances in which inventory is depleted to -0-, then restocked.
Ex
- if I have an item that was in inventory on 1/1/2020 and remained in inventory until the item was depleted to 0 on 3/1/2020, and later returned to stock on 4/1/2020, I would like the results of my SQL to show 4/1/2020 as the most recent inventory date. I cannot use the MIN function, as that would return my initial date of
1/1/2020 and inaccurately reporting current product aging. In this scenario, my aging result would be current date - most recent inventory date of 4/1/2020 to arrive at 6 days .

If an item is constantly in stock and never reaches -0-, then great, min works fine. However, it is the instances in which stock is depleted to -0-, then later restocked at a later date(s) that I am struggling with obtaining the values, as I need the most recent restock date.

Not really sure how to resolve? Some type of row_number over partition? Not certain.

Thanks again for any assistance!

 

[theDBguy]

Hi. You'll probably have to do this in steps. For example,

1. Get the max date, this should give you the last inventory
2. Get the min inventory where the max date is before the date in step 1
2. Get the min date where the date is after the date in step 2

Just a thought.

Sent from phone...

 

[plog]

Query1 - Get the Minimum date for every product:

SELECT product_id, MIN(inventory_dt) AS DateFirst FROM sellable_prod_inv

Query2 - Find the last time a product went to 0 inventory:

SELECT product_id, MAX(inventory_dt) AS Date0 FROM sellable_prod_inv WHERE Inventory=0

Query3 - Get the next date for those in Query2:

SELECT Query2.product_id, MIN(sellable_prod_inv) AS DateInInventory
FROM Query2 INNER JOIN sellable_prod_inv ) ON Query2.product_id = sellable_prod_inv.product_id
WHERE Query2.Date0 > sellable_prod_inv

Query4 - combine Query1 & 3 and use the date from Query3 if it exists:

SELECT Query1.product_id, IIf(IsNull(Query3.DateInInventory), Query1.DateFirst, Query3.DateInInventory) AS InventoryDate
FROM Query1 LEFT JOIN Query3 ON Query1.product_id = Query3.product_id

I'm pretty sure Query2 and Query3 can be combined to get this thing down to just 3 queries. Also, I hand coded all that SQL so no guarantee that you can just copy and paste it.

 

[arnelgp]

i dont see why it is complicated. at any inv qry, whether 0 or has stock, inv date is retrieved using Max() function on your query.

 

[HeelNGville]

i dont see why it is complicated. at any inv qry, whether 0 or has stock, inv date is retrieved using Max() function on your query.

The inventory source table refreshes daily, and retains a snapshot of the inventory values for each refresh date. If I use the max() on the inventory_dt the results returned will be the current snapshot date of inventory. Example.

Date Product On Hand Qty
4/7/2020 ABC 2
4/6/2020 ABC 4
4/5/2020 ABC 10
4/4/2020 ABC 15
4/3/2020 ABC 20
4/2/2020 ABC 30
3/29/2020 ABC 1

So, if we use MAX in the example above, we would retrieve 4/7/2020 as my aging inventory date, and if we use the MIN, we would retrieve 3/29/2020, however, as shown in the example, there was no inventory present (break in inventory dates) for the item on 3/30/2020-4/1/2020, so my last inventory date for calculating age should be current date of 4/7/2020 - 4/2/2020 = 5 days. This is a high level example, but multiple instances could display a gap in inventory of months between in/out of stock, so I need to depict the most recent introduction into inventory as the starting date.

 

[HeelNGville]

Thanks for the reply DB Guy and plog.

-Plog, regarding query 2, once the inventory reaches -0-, there is no record found in the source. Example in reply above to arnelgp.

If a product retains inventory on dates that are consecutive from beginning period to current date, then great, the MIN works as designed. But, any breaks in consecutive dates, I need to pick up the first date after the break and new inventory is introduced, as my aging would be based on that value.

 

[Galaxiom]

Generate a field with all dates in the range of interest and outer join that to the inventory query so that any gaps in the dates get a Null in the Quantity field. Then proceed as per the previous advice.

A list of all dates can be generated using Tally techniques of which there are many variants.

 

[arnelgp]

check Query1 and Query2.
this may give you a lead.

 

[plog]

I suggest you demonstrate your issue with just data. Provide 2 sets of data:

A. Starting data from your table. Include table and field names and enough data to cover all cases. Your last post is a good start, but I suggest more products to demonstrate more cases (never replenished, replenished twice in a row, etc.)

B. Expected results of A. Show us what data you expect your query to produce based on the data in A.

Again, 2 sets of data. Don't walk us through it, just give us starting and expected data.

 

[The_Doc_Man]

Perhaps this, with a little bit of cheating (to be explained later):

SELECT MIN( InvDate ) 
FROM InvTable AS Y 
WHERE 
    ( Y.PRODID=<specify product here>) AND 
    ( Y.InvDate >
        ( SELECT 
            MAX( InvDate ) 
            FROM InvTable AS X 
            WHERE 
                ( X.PRODID=<specify product here>) AND 
                ( X.InvAmount = 0  ) ) ) ;

So... part 1 of this approach finds the minimum inventory date from the subset of records that has an inventory date greater (but not equal to) the highest date where the amount is 0.

The "cheat" is that for EVERY product you have, create a record with 0 inventory for the day BEFORE the first date that product EVER appears in your inventory system. That would be a fairly simple query.

INSERT INTO InvTable AS A
    (A.PRODID, A.PRODNAME, A.QTYONHAND, A.INVDATE, etc. ) 
    SELECT 
        B.PRODID, B.PRODNAME, 0, (B.INVDATE - 1), etc. 
    FROM InvTable AS B
    WHERE B.INVDATE = 
        ( SELECT MIN( C.INVDATE )
        FROM INVTABLE AS C 
        WHERE C,PRODID = B.PRODID ) ;

In English, this copies the earliest record for each product but subtracts 1 from the date on that record and provides 0 for the on-hand amount.

All the "cheat" does is assure that each product has at least one day with 0 on-hand on the day before that product first appears. This removes the complication about cases where there is never a 0 date in the inventory of that product. I.e. you don't need a third query or a complex IIF to handle the "never zero" case. And, to be honest, those records I'm suggesting you insert are NOT lies. Because if they are dated for the day BEFORE that product was introduced to your system, then on that date your quantity on hand was, indeed, 0. So it isn't a lie.

It might be a really good idea to try that 2nd query on a COPY of your DB just in case I screwed the pooch.