mysql - Find the number of days before instock level goes negative

Question

I want to find the number of days from my SQL table before my instock level goes negative. Is there a way to do that on SQL? I can find the number of days that have negative instock levels but I am not able to figure out a way to stop the counter for days when instock goes negative.

create TEMPORARY VIEW instock_ssd_fc as
(    select
        ilbo.asin,
        ilbo.snapshot_day,
        sum(coalesce(ilbo.on_hand_quantity, 0) - coalesce(greatest(ilbo.allocated_quantity, ilbo.bound_quantity), 0)) as inventory_units_ssd_fc,
        sum(coalesce(ilbo.in_transit_quantity, 0)) as in_transit_quantity_ssd_fc,
        sum(coalesce(ilbo.unallocated_customer_demand,0)) as unallocated_customer_demand_ssd_fc,
        sum(case when iog.inventory_owner_group_id is not null then coalesce(ilbo.on_hand_quantity, 0) - coalesce(greatest(ilbo.allocated_quantity, ilbo.bound_quantity), 0) else 0 end) as inventory_units_retail_ssd_fc
    from d_unified_inv_level_by_owner as ilbo
    inner join asin_selected as a
        on a.asin = ilbo.asin
    left join iog
        on ilbo.inventory_owner_group_id = iog.inventory_owner_group_id
        and ilbo.region_id = ${region_id}
    where ilbo.region_id = ${region_id}
        and upper(ilbo.inventory_condition_code) = 'SELLABLE'
        and ilbo.snapshot_day between cast('${date}' as timestamp) - interval 1 weeks and cast('${date}' as timestamp)
        and ilbo.warehouse_id = '${fc}'
    group by ilbo.asin, ilbo.snapshot_day
);

From the above query, I want to check how many days the ASIN had a positive inventory. For example, if I start looking for instock levels from 12/27 and go behind, if on snapshot_day of 12/06 the inventory level goes to negative for the first time starting from 12/27, my number of days will be 21.

question from:https://stackoverflow.com/questions/65837122/find-the-number-of-days-before-instock-level-goes-negative

Answer 1

Assuming your table has a column date for the date and instock for the quantity, you could use try:

SELECT COUNT(*)
FROM yourTable
WHERE date < (SELECT date FROM yourTable
              WHERE instock < 0
              ORDER BY date LIMIT 1);