Сверка банковской выписки с оплатами в SQL: найдём пропуски и расхождения

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

Сначала выбираем надёжный ключ

В примере платёжный провайдер передаёт номер операции ref в обе системы. Именно он связывает записи. Дата и сумма остаются контрольными полями: одинаковые суммы встречаются у разных покупателей, а дата зачисления может отличаться от даты оплаты. Автоматическое сопоставление только по сумме способно закрыть чужой заказ.

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

Почему дубли надо показать до соединения

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

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

Полный пример для SQLite

Запрос создаёт учебные таблицы и объединяет множество ключей через UNION. Поэтому в результат попадут записи, присутствующие только в одном источнике. Отсутствие определяем по числу найденных строк; COALESCE для денежных полей оставляем только там, где оно не стирает смысл пропуска. Запустите весь блок в пустой SQLite-базе.

CREATE TABLE bank(ref TEXT, amount INTEGER);
CREATE TABLE ledger(ref TEXT, amount INTEGER);
INSERT INTO bank VALUES ('p1',10000),('p2',19000),('p3',5000),('p5',3000),('p5',3000);
INSERT INTO ledger VALUES ('p1',10000),('p2',20000),('p4',7000),('p5',3000);
WITH b AS (
 SELECT ref,COUNT(*) AS n,SUM(amount) AS amount FROM bank GROUP BY ref
), l AS (
 SELECT ref,COUNT(*) AS n,SUM(amount) AS amount FROM ledger GROUP BY ref
), keys AS (SELECT ref FROM b UNION SELECT ref FROM l)
SELECT k.ref,b.amount AS bank_amount,l.amount AS ledger_amount,
 CASE WHEN COALESCE(b.n,0)>1 OR COALESCE(l.n,0)>1 THEN 'duplicate'
      WHEN b.n IS NULL THEN 'only_ledger'
      WHEN l.n IS NULL THEN 'only_bank'
      WHEN b.amount<>l.amount THEN 'amount_mismatch'
      ELSE 'matched' END AS status
FROM keys k LEFT JOIN b ON b.ref=k.ref LEFT JOIN l ON l.ref=k.ref
ORDER BY k.ref;

Как читать результат

Операция p1 подтверждена. По p2 банк показывает 190 рублей, реестр — 200: нужно проверить комиссию, частичную оплату или неверную сумму. p3 есть только у банка, p4 только в реестре. По p5 сначала разбираем повторный номер: сумма двух строк банка сама по себе ещё не доказывает двойное поступление.

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

Из сверки должна получиться очередь действий

Передайте финансовой команде четыре списка исключений, а не один столбец «не сошлось». Записи only_bank требуют поиска заказа или неразобранного аванса; only_ledger — проверки статуса операции и периода выгрузки. amount_mismatch обсуждается с учётом договора провайдера. duplicate возвращается владельцу исходных данных.

Сохраните дату среза и версии обоих файлов. Затем повторите расчёт после исправления: исчезновение исключения должно объясняться изменением конкретной строки. У сверки полезно считать количество ключей каждого статуса и сумму операций каждой стороны отдельно. Разница общих итогов не заменяет эти показатели: две противоположные ошибки могут взаимно погаситься.

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

Как принять исправление

Возьмите p2 и запросите первичный документ. Если 10 рублей удержаны как комиссия, в реестре должны остаться валовая оплата 200 рублей и отдельная комиссия, а банковский источник надо привести к тому же уровню сравнения. Если покупатель действительно внёс 190 рублей, исправлять нужно сумму оплаты либо ожидать доплату. Одинаковый статус расхождения ведёт к разным действиям в зависимости от основания.

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

Продолжить практику

После этой сверки следующий шаг — собрать финансовый отчёт из нескольких источников и проверить, что соединения сохранили суммы. В курсе «Продвинутый SQL» вы разберёте сложные JOIN, сверку отчётов и финансовые задачи, затем соберёте аналитическую витрину интернет-магазина. Откройте программу платного курса.