Проверка счетов в SQL: цена в документе против договорного тарифа

Поставщик выставляет цену по объёму: до ста единиц одна ставка, от ста — другая. На границе диапазона легко применить неверную цену. Вместо ручной проверки каждого счёта построим сверку в SQLite. Она покажет разницу суммы и отдельно выделит случаи, когда тариф вообще не удалось определить однозначно.

Какой тарифный договор проверяем

В примере ставка диапазона применяется ко всему объёму счёта. При количестве 100 каждая единица стоит восемь копеек, итог равен 800 копейкам. Это не прогрессивный расчёт, где первые 99 единиц оплачиваются по десять, а остаток по восемь. Перед написанием SQL нужно прочитать именно это условие договора: обе модели встречаются в бизнесе и дают разные суммы.

Диапазоны задаём как полуоткрытые: min_qty включён, max_qty исключён. Поэтому количество 100 относится только к ступени [100,1000), а не к предыдущей [0,100). Такой договор устраняет пограничное двойное соответствие. Для открытой верхней ступени можно использовать NULL и отдельное условие, но в нашем примере тариф выше 999 единиц не задан.

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

Одна подходящая ставка — обязательное условие

Просто LEFT JOIN по диапазону недостаточен. Пересекающиеся ступени способны дать две строки на один счёт и раздуть суммы. Поэтому сначала считаем число совпадений для каждого документа. Только при одном совпадении рассчитываем ожидаемую сумму; ноль означает missing_tariff, больше одного — ambiguous_tariff.

MAX(rate) в этом расчёте не выбирает самую выгодную ставку. При одном совпадении он извлекает единственное значение после группировки. При нескольких совпадениях ожидаемая сумма остаётся NULL, чтобы неоднозначность не превратилась в произвольное финансовое решение. В реальном справочнике к диапазону добавляются услуга, валюта, договор и период действия.

WITH invoices(id,qty,billed) AS (
 VALUES (1,50,500),(2,100,900),(3,150,1200),(4,1500,10000)
), tariffs(min_qty,max_qty,rate) AS (
 VALUES (0,100,10),(100,1000,8)
), matched AS (
 SELECT i.id,i.qty,i.billed,COUNT(t.rate) AS matches,
 CASE WHEN COUNT(t.rate)=1 THEN i.qty*MAX(t.rate) END AS expected
 FROM invoices i LEFT JOIN tariffs t ON i.qty>=t.min_qty AND i.qty<t.max_qty
 GROUP BY i.id,i.qty,i.billed
), checked AS (
 SELECT *,CASE WHEN matches=0 THEN 'missing_tariff'
 WHEN matches>1 THEN 'ambiguous_tariff'
 WHEN billed<>expected THEN 'mismatch' ELSE 'ok' END AS status
 FROM matched
)
SELECT id,qty,billed,expected,billed-expected AS delta,status
FROM checked WHERE status<>'ok' ORDER BY id;

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

[[2, 100, 900, 800, 100, "mismatch"], [4, 1500, 10000, null, null, "missing_tariff"]]

Что отправить на разбор

Счёт 2 завышен на 100 копеек: для количества 100 действует ставка восемь, ожидаемая сумма 800 вместо 900. Счёт 4 не получил ставку, потому что объём 1500 вне справочника. Его нельзя объявить верным или неверным по сумме до уточнения договора. Счета 1 и 3 прошли проверку и не попали в очередь исключений.

Для автоматического контроля полезно сохранять не только ошибочные счета, но и число успешно сопоставленных документов. Если новая версия справочника внезапно оставила половину объёмов без тарифа, это проблема правил, а не массовая ошибка поставщика. Отдельная проверка перекрытия диапазонов поможет обнаружить её ещё до поступления счетов.

Как расширить сверку на рабочие документы

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

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

Если ставки меняются со временем, нужен выбор записи на дату услуги: Исторический JOIN в SQL: статус клиента на дату заказа.

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

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