Заказ оплачен, но не отгружен полностью: отчёт SQL для операций

Заказ оплачен, но клиент получил только часть товаров. В отчёте «есть отгрузка» такой заказ выглядит выполненным. Для операционной очереди нужно сравнить количество каждой позиции с фактически переданным количеством. Сделаем это в SQLite, сохраняя как недопоставку, так и превышение заказа.

Сравниваем количества на правильном ключе

Единица результата — товарная позиция заказа. В примере ключом служит order_id вместе с sku. Если один товар может стоять в заказе несколькими строками с разными условиями, понадобится line_id. Сводка только по заказу способна скрыть ситуацию, когда один товар отгрузили лишний раз, а другой не отправили вовсе.

В shipments несколько передач относятся к одной позиции. Сначала суммируем их до общего ключа, затем соединяем с заказом. Нельзя соединять каждую товарную строку с каждой отгрузкой по одному order_id: разные артикулы начнут перемножаться. Таблица ordered в примере уже содержит только оплаченные позиции без отменённого количества.

Количество считается в одной единице измерения. Если заказ ведётся в коробках, а склад передаёт штуки, перед сверкой нужен коэффициент упаковки. Он должен относиться к конкретному товару и версии упаковки. Простая разность чисел в разных единицах даст правдоподобную, но бессмысленную недопоставку.

Сохраняем знак расхождения

Остаток к отправке равен ordered_qty−shipped_qty. Положительный остаток означает недопоставку, отрицательный — превышение. Второе нельзя обрезать до нуля: оно может указывать на повторную регистрацию документа, неверное количество или реальную лишнюю передачу. Для склада это отдельный маршрут разбора.

Отсутствие подходящей отгрузки означает ноль только при полной выгрузке склада на дату снимка. Подтверждённые, но ещё не проведённые документы стоит показывать отдельно от фактического движения. В пример входят только подтверждённые передачи, поэтому COALESCE корректно превращает отсутствие суммы в ноль.

WITH ordered(order_id,sku,qty) AS (
 VALUES (1,'A',5),(1,'B',2),(2,'A',3),(3,'C',1)
), shipments(order_id,sku,qty) AS (
 VALUES (1,'A',1),(1,'A',2),(1,'B',2),(2,'A',4)
), shipped AS (
 SELECT order_id,sku,SUM(qty) AS qty FROM shipments GROUP BY order_id,sku
), compared AS (
 SELECT o.order_id,o.sku,o.qty AS ordered_qty,COALESCE(s.qty,0) AS shipped_qty,
 o.qty-COALESCE(s.qty,0) AS remaining
 FROM ordered o LEFT JOIN shipped s USING(order_id,sku)
)
SELECT order_id,sku,ordered_qty,shipped_qty,remaining,
 CASE WHEN remaining>0 THEN 'under' ELSE 'over' END AS issue
FROM compared WHERE remaining<>0 ORDER BY order_id,sku;

Ожидаемые строки результата в порядке столбцов SELECT:

[[1, "A", 5, 3, 2, "under"], [2, "A", 3, 4, -1, "over"], [3, "C", 1, 0, 1, "under"]]

Как читать операционную очередь

В заказе 1 по A нужно отправить ещё две единицы, по B всё выполнено. В заказе 2 обнаружена одна лишняя A. Заказ 3 вообще не имеет отгрузки C и сохраняется благодаря LEFT JOIN. Если использовать INNER JOIN, самая очевидная проблема исчезла бы из результата.

Для работы менеджера добавьте дату обещанной доставки и контакт ответственного. Сортировать очередь полезнее по просрочке обещания и важности заказа, чем только по количеству. При этом исходные заказанное и отгруженное количества оставляют рядом: сотрудник должен видеть, из чего возникло требование.

Что проверить перед доотгрузкой

Сверьте документы и физическое движение, особенно для отрицательного остатка. Повторный документ нельзя исправлять отправкой недостающего товара по другой позиции. Если отгрузка была возвращена клиентом, решите, уменьшает ли она исполненное количество или относится к отдельному процессу возврата. Правило зависит от того, ждёт ли клиент замену.

После закрытия очереди повторите запрос на новом снимке. Сохранение полного ключа позиции позволяет отследить, какие недопоставки исчезли, а какие вернулись после корректировки. Для финансовой сверки количества дополняют оплатами, но денежное покрытие и физическая комплектность остаются разными проверками.

После физической сверки отдельно проверяют денежные операции заказа: Частичные возвраты в SQL: как найти заказ, которому вернули больше оплаты.

Следующий уровень SQL-практики

В «Продвинутом SQL» вы учитесь соединять источники разной детализации и находить причины дублей и неверных сумм. Курс включает сверку отчётов и цельные кейсы на SQLite. Запросы проверяются автоматически, а итоговый проект объединяет расчёты в аналитическую витрину магазина. Откройте программу «Продвинутого SQL».