Find out extra quantity available in a table-CodePudding

I have two tables, let's say OrderPlaced and OrderDelivered.

The OrderPlaced table looks like this:

In a single order we can have multiple products(which is defined by sku in the table) and each product can have multiple quantity.

The OrderDelivered table looks like this:

So technically 3 products have not been delivered. Orderid 1000 - product S101, Orderid 1001 - product S102(as 3 quantity required, but 2 delivered) and Orderid 1002 - product S100.

I am trying to write a SQL query that can give me the OrderId and sku those have not been delivered. For now I have written something like

select OrderPlaced.orderid,OrderPlaced.sku
from OrderPlaced 
left join OrderDelivered
  on OrderPlaced.Orderid = OrderDelivered.orderid and OrderPlaced.sku = OrderDelivered.sku
where OrderDelivered.sku is NULL;

This is giving me Orderid 1000 - product S101 and Orderid 1002 - product S100, but Orderid 1001 - product S102 is missing. I understand I have to do a check on qty as well, but couldn't think how to do that. I would really appreciate it if someone can help me with that part.

CodePudding user response：

Add up the deliveries per order and sku and then outer join the delivered quantities to the order table so you can compare the quantities.

select
  p.orderid,
  p.sku,
  p.qty as ordered,
  coalesce(d.sum_qty, 0) as delivered
from orderplaced p
left join
(
  select orderid, sku, sum(qty) as sum_qty
  from orderdelivered
  group by orderid, sku
) d on d.orderid = p.orderid and d.sku = p.sku
where p.qty > coalesce(d.sum_qty, 0)
order by p.orderid, p.sku;

CodePudding user response：

Your query works for any items that have not been delivered at all, this is your WHERE OrderDelivered.sku IS NULL. But you can also have a scenario in which fewer items are delivered than ordered, and importantly, you can have multiple records related to your deliveries even if they refer to the same order and sku (two rows with 1 qty each).

In this case you will need to sum up all the deliveries per placed order id, sku and quantity (GROUP BY clause in the query below) check if that sum (or 0 if nothing is found) differs from the placed order (HAVING clause). You could use such a query:

SELECT OrderPlaced.orderid, OrderPlaced.sku,
       OrderPlaced.qty - COALESCE(SUM(OrderDelivered.qty), 0) AS qty_missing,
       CASE 
         WHEN SUM(OrderDelivered.qty) IS NULL
         THEN 'Yes' 
         ELSE 'No' 
       END AS is_missing_completely
  FROM OrderPlaced 
  LEFT 
  JOIN OrderDelivered
    ON OrderPlaced.Orderid = OrderDelivered.orderid 
   AND OrderPlaced.sku = OrderDelivered.sku
 GROUP BY OrderPlaced.orderid, OrderPlaced.sku, OrderPlaced.qty
HAVING OrderPlaced.qty != COALESCE(SUM(OrderDelivered.qty), 0)

Here's a live demo on dbfiddle

CodePudding user response：

I would create two aggregated representations of your ordered and delivered products, and then outer join them to get the differences. If you are using MySql 8 you can represent these as a CTE, otherwise just use two equivalent sub-queries

with op as (
    select OrderId, Sku, Sum(qty) Qty
    from OrderPlaced
    group by OrderId, Sku
), od as (
    select OrderId, Sku, Sum(qty) Qty
    from OrderDelivered
    group by OrderId, Sku
)
select op.OrderId, op.Sku, op.Qty - Coalesce(od.qty,0) notDelivered
from op
left join od on od.orderid = op.orderid and od.sku = op.sku
where op.Qty - Coalesce(od.qty,0)>0;

Example DB<>Fiddle