I'll be very happy if someone may help me with this problem.

I have a MySQL database with a table called stock_log with this data:

So, I need to make a query that counts how many days a SKU is without stock to today (2021-11-23), only if today is without stock, grouped by SKU. Also it must count the days without stock from the last date when it has no stock (because a SKU could have stock 3 consecutive days, then again 0, then again it has stock and then again 0).

So the query should shows:

NOTES:

• SKU 111: 0 days without stock from today. 123
• SKU 123: 2 days without stock from 2021-11-21 (the last date without stock) to today.
• SKU 146: Don't show in this result because today it has stock again from today.

I hope to explain it well.

Thanks for your help! :)

CodePudding user response：

I think it's a real g thing to do if you have a sql question to build the sql fiddle schema for any potential answerers.

Anyway, something like:

select 
a.sku,
datediff(current_date, date_last_stock) - 1 as days_wo_stock
from stock a
left join (
  select 
  sku, 
  max(date) date_last_stock
  
  from stock 
  
  where qty > 0
  
  group by 
  sku
 ) b on a.sku = b.sku

where a.date = current_date
and a.qty = 0

See this fiddle. Works on MySQL 5.6

CodePudding user response：

You can solve the problem using next query:

select
    sku, 
    DATEDIFF( -- date diff in days between
        CURRENT_DATE,  -- current date
        MAX(if(quantity > 0, `date`, '')) -- last date with stock
    ) days_without_stock
from sku
group by sku
having days_without_stock > 0;

MySQL group by fiddle