Adding min qty on running total per day

Question

I have a query that gives the running total of stock less sales on a daily basis and need to show the min available quantity to ensure that sales do not oversell. In Excel I can run min on a range, what is the equivalent for SQL?

Answer 1

Did you mean you want the quantity sold over time (Sales column in the table below) and ensure that it doesn't exceed the available_to_sell amount?

Reference code:

WITH
  a AS (
  SELECT
    *,
    LAG(daily_total) OVER (PARTITION BY stockcode ORDER BY date) AS aa,
    LAG(Available_To_Sell) OVER (PARTITION BY stockcode ORDER BY date) AS a_t_s_lag
  FROM
    `optimal-weft-418017.78207210.78207210_01`
  ORDER BY
    stockcode,
    date
  LIMIT
    100),
  b AS (
  SELECT
    *,
    CASE
      WHEN aa-daily_total < 0 THEN 0
    ELSE
    aa-daily_total
  END
    AS Sales
  FROM
    a
  ORDER BY
    stockcode,
    date)
SELECT
  *EXCEPT(a_t_s_lag,
    aa),
  CASE
    WHEN Sales>a_t_s_lag THEN 'Exceeds Available Qty'
    WHEN Sales IS NULL THEN NULL
  ELSE
  'OK'
END
  AS QA
FROM
  b
ORDER BY
  stockcode,
  date

Output Table:

StockCode	Date	Daily_Total	Available_To_Sell	Sales	QA
Item 3A	2020-01-01	23	6	null	null
Item 3A	2024-03-19	22	6	1	OK
Item 3A	2024-03-20	20	6	2	OK
Item 3A	2024-03-21	15	6	5	OK
Item 3A	2024-03-25	10	6	5	OK
Item 3A	2024-03-26	6	6	4	OK
Item 3A	2024-03-28	8	6	0	OK
Item 3A	2024-04-04	7	6	1	OK
Item 3A	2024-04-08	7	6	0	OK
Item 3A	2024-04-09	6	6	1	OK
Item 3A	2024-04-10	11	11	0	OK
Item 3A	2024-04-15	11	11	0	OK
Item 3A	2024-04-22	11	11	0	OK
Item 4A	2020-01-01	4	4	null	null
Item 4A	2024-03-20	3	3	1	OK
Item 4A	2024-03-25	1	1	2	OK
Item 4A	2024-03-26	1	1	0	OK