Determine expected quantity on hand and quantity expired for inventory given upcoming sales orders and item lot expirations

Question

Thank for your checking this one out. I need a SQL Server query that will return a list by date of the expected quantity of items on hand after upcoming sales along with the quantity of items that will expire if unsold.

The inventory is stored in lots that have an expiration date. In this example, we have 2 lots. Lot_id(50) has qty 20 items that will expire on 2022-12-13 if they are not sold, and Lot_id(52) has qty 40 items that will expire on 2022-12-14 if they are not sold.

Currently, there are 3 orders: Qty 10 on 2022-12-11, Qty 20 on 2022-12-12, and Qty 25 on 2022-12-13.

Given two tables, inventory and sales.

Inventory:

lot_id	item_id	quantity_on_hand	lot_expiration_date
50	1	20	2022-12-13
52	1	40	2022-12-14

Sales:

order_id	item_id	order_date	order_qty
100	1	2022-12-11	10
101	1	2022-12-12	20
102	1	2022-12-13	25

Exhibit A - Daily Journal of sales for item_id = 1

Date	Expiration_Qty	Order_Qty	Lot_id_50	Lot_id_52	Unexpired_Qty_On_Hand	Expired_Qty_On_Hand
2022-12-10		0	20	40	60	0
2022-12-11		10	10	40	50	0
2022-12-12		20	0	30	30	0
2022-12-13	20	25	0	5	5	0
2022-12-14	40	0	0	0	0	5

Exhibit B - Daily Journal explanation by day

2022-12-10 There is a total of 60 qty on hand (total unexpired from all lots), no orders, and no expirations
2022-12-11 There is a sales order for 10 qty, and no expirations
2022-12-12 There is a sales order for 20 qty, and no expirations
2022-12-13 There is a sales order for 25 qty, and 20 qty from Lot_id_50 would have expired had they not sold.
2022-12-14 There are no sales orders, and the remaining qty (5) in Lot_id_52 expires

The desired result with show the item_id, along with expected unexpired Qty on hand and the expected qty that will expire by date.

Date	item_id	Unexpired_Qty_On_Hand	Expired_Qty_On_Hand
2022-12-10	1	60	0
2022-12-11	1	50	0
2022-12-12	1	30	0
2022-12-13	1	5	0
2022-12-14	1	0	5

Code:

-- SQL to create tables and populate
CREATE TABLE `sales` 
(
    `order_id` int(11) NOT NULL,
    `item_id` int(11) NOT NULL,
    `order_date` date NOT NULL,
    `order_quantity` int(11) NOT NULL
);

INSERT INTO `sales` (`order_id`, `item_id`, `order_date`, `order_quantity`) 
VALUES (100, 1, '2022-12-11', 10),
       (101, 1, '2022-12-12', 20),
       (102, 1, '2022-12-13', 25);

CREATE TABLE `inventory` 
(
    `lot_id` int(11) NOT NULL,
    `item_id` int(11) NOT NULL,
    `quantity_on_hand` int(11) NOT NULL,
    `lot_expiration_date` date NOT NULL
);

INSERT INTO `inventory` (`lot_id`, `item_id`, `quantity_on_hand`, `lot_expiration_date`) 
VALUES (50, 1, 20, '2022-12-13'),
       (52, 1, 40, '2022-12-14');

Answer 1

If the business never sells expired items to customers, which means a sales order is always taken if there's sufficient unexpired_qty_on_hand.

Then,

Step 1. Get a list of dates based on given report date ranges cte_report_date

Step 2. Calculate unexpired_qty_on_hand and expired_qty_on_hand by report_date, lot_id, item_id in cte_inventory_by_date_lot

Step 3. Aggregate the above to report_date and item_id level in cte_inventory_by_date

Step 4. Calculate sales_quantity and total_sales_quantity (cumulative sales qty) by order_date and item_id in cte_sales_by_date

Step 5. Generate all combination of (report_date, item_id) for reporting

Step 6. From the above left join to cte_sales_by_date to get sales by report date

Step 7. Join and compare inventory and sales to calculate [unexpired | expired]_qty_on_hand by report_date x item_id

with cte_report_date as (
select cast('2022-12-10' as date) as report_date, 0 as n
union all
select dateadd(day, 1, r.report_date), n+1
  from cte_report_date r
 where n < 4),
cte_inventory_by_date_lot as (
select r.report_date,
       i.lot_id,
       i.item_id,
       case 
          when i.lot_expiration_date >= r.report_date then i.quantity_on_hand
          else 0
       end as unexpired_qty_on_hand,
         case 
          when i.lot_expiration_date < r.report_date then i.quantity_on_hand
          else 0
       end as expired_qty_on_hand          
  from cte_report_date r, inventory i),
cte_inventory_by_date as (
select report_date,
       item_id,
       sum(unexpired_qty_on_hand) as unexpired_qty_on_hand,
       sum(expired_qty_on_hand)   as expired_qty_on_hand
  from cte_inventory_by_date_lot
 group by report_date, item_id),
cte_sales_by_date as (
select order_date, 
       item_id, 
       sum(order_quantity) as sales_quantity
  from sales
 group by order_date, item_id),
cte_report_item as (
select r.report_date,
       s.item_id
  from cte_report_date r, (select distinct item_id from cte_sales_by_date) s),
cte_sales_by_report_date as (
select r.report_date,
       r.item_id,
       coalesce(s.sales_quantity, 0) as sales_quantity,
       sum(coalesce(s.sales_quantity, 0)) over (partition by r.item_id order by r.report_date) as total_sales_quantity
  from cte_report_item r
  left
  join cte_sales_by_date s
    on r.report_date = s.order_date
   and r.item_id = s.item_id)
select i.report_date,
       i.item_id,
       case
          when i.unexpired_qty_on_hand < s.total_sales_quantity then 0
          else i.unexpired_qty_on_hand - s.total_sales_quantity
       end as unexpired_qty_on_hand,
       case
          when i.unexpired_qty_on_hand < s.total_sales_quantity then i.expired_qty_on_hand - (s.total_sales_quantity - i.unexpired_qty_on_hand)
          else i.expired_qty_on_hand
       end as expired_qty_on_hand
  from cte_inventory_by_date i
  join cte_sales_by_report_date s
    on i.report_date = s.report_date
   and i.item_id = s.item_id
 order by report_date, item_id;

Outcome:

report_date|item_id|unexpired_qty_on_hand|expired_qty_on_hand|
-----------+-------+---------------------+-------------------+
 2022-12-10|      1|                   60|                  0|
 2022-12-11|      1|                   50|                  0|
 2022-12-12|      1|                   30|                  0|
 2022-12-13|      1|                    5|                  0|
 2022-12-14|      1|                    0|                  5|

The query may not cover all use cases outside of the provided sample data but hopefully it gets you started.