Всем привет.
Помогите пожалуйста с запросом. Недосып и борьба со змеем не дают сосредоточиться, а работу работать надо.
Есть таблица договоров, предположим, contracts с пк id_contract.
Есть таблица графика платежей, пусть будет schedules, фк - id_contract, sched_date - дата платежа по графику, sched_sum - ожидаемая сумма.
Есть таблица платежей, например, payments. ФК - id_contract, paym_date - дата платежа, paym_sum - сумма платежа.
Даты графика и реальных платежей могут не совпадать. Один платеж может быть меньше или больше суммы ожидаемого платежа. График и платежи между собой связаны только через id_contract.
Необходимо выбрать записи из графика платежей с неполной оплатой или просрочкой платежа на определенную дату.

Я бы связочку по FIFO пробросил всё-таки. Хотя бы какой регламентной процедурой.

pasha_usue
Я бы связочку по FIFO пробросил всё-таки

Это не по моей части, за это архитекторы отвечают.

В черновом варианте получается нечто вроде:
Всем спасибо за помощь.

Надо было бы тестовых данных дать (содержимое и ожидаемый результат)...
По логике вопрос есть...
Нужно для КАЖДОГО платежа по графику определять была ли на его дату просрочка/недоплата? Или только для КРАЙНЕГО с датой меньше указанной?
Т.е. если "график" был (в условных "днях от старта", это не суть важно)
1 100
5 100
10 100
15 50
20 50
25 50
30 50

а оплаты были
1 100 -- вовремя вся сумма
4 80 -- аванс не полный
6 30 -- просрочка на 1 день, сумма с переплатой
10 40 -- неполная оплата
15 50 -- если считать как доплата к 10-му, то на 15 просрочка, если как оплату по 15-му, то остаётся недоплата по 10-му
16 50 -- погашены все долги до 15-го
20 40 -- неполная оплата за 20-е
26 60 -- погашены долги за 20-е и 25-е, но с просрочкой на 1 день
31 50 -- просто просрочка на 1 день

То какой результат должен быть для разных "отсекающих" дат? Пример для одного договора...

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

Если интересует лишь "состояние на дату" (есть ли на такое то число просроченная задолженность или нет) - тогда аналитика не нужна - достаточно посуммировать все "плановые" до этой даты и все "фактические" и сравнить их.

Это да. Если бы я был по ту сторону экрана, меня, наверное, интересовала бы более широкая статистика, в т.ч. выделение просроченных на момент расчетной даты платежа, но погашенных на данный момент. Но мне, похоже, повезло. Фильтр даты влияет только на даты договоров, график неоплаченных платежей нужно выбрать на текущий момент (дату расчета). Последний платеж покрывает сначала более ранние просрочки, остатком закрывает текущий платеж, т.е. логика пока простейшая. Иными словами, из графика платежей нужно убрать все записи, которые полностью покрыты общей суммой платежей с сортировкой по датам.
Возможно, потом будет задача на доработку, но пока что так.



Исправлено 1 раз(а). Последнее : Pekpytep, 28.12.17 14:55

Т.е. тебе нужно сначала взять из графика все платежи до указанной даты, а потом последовательно выкидывать их до достижения текущей суммы оплаты? Получится что останутся только неоплаченные и, возможно, одна частично оплаченная? При том сумма оплаты не ограничивается (ну по крайней мере в твоём запросе этого нет) датами - т.е. берутся ВСЕ оплаты, так?
Если делать это по одному договору, т.е. как у тебя в примере - с параметром p_id_contract, то по идее твой запрос вполне подходит под такую логику. Если потребуется по всем договорам (ну или по части, но не по одному) - придётся вносить patrition by в подзапрос и ограничивающее условие переделать (лучше прописать одним подзапросом подсчёт сумм оплат по договорам, а потом соединить обе части "хитрым способом")...
P.S. только сейчас заметил что и в запросе к "графику" ты не ограничиваешь ничего по дате Т.е. всё работает по полному массиву данных - грубо говоря "на текущий момент времени"...





Исправлено 1 раз(а). Последнее : Igor Korolyov, 28.12.17 16:18

Igor Korolyov
Т.е. тебе нужно сначала взять из графика все платежи до указанной даты, а потом последовательно выкидывать их до достижения текущей суммы оплаты?

Yep, почти. Список договоров уже ограничен фильтрами дат, подразделений и т.д. и т.п. График и платежи по ТЗ датами не ограничены, т.е. туда попадут в том числе, даты большие сегодняшней, если они есть. Если понадобится ограничить по дате, то глобальных переделок не понадобится - в двух местах добавить в where фильтр по верхней границе даты. Я умышленно все максимально упростил для более легкого понимания. Приведенный кусок кода является коррелирующим подзапросом, а полный текст запроса over 700 строк.
По partition by - согласен, по соединению "хитрым способом" - не очень понятно. Можно подробнее?

Ну там надо соединить обе части по id_contract, а потом ещё и по твоему условию accum_sum > sum_paym_sum. И не забыть про договора для которых вообще ещё нет оплат - т.е. второй подзапрос по такому id_contract будет пуст - т.е. потребуется LEFT JOIN.
Хотя, в реальном коде второй подзапрос может быть составлен так что вернёт и записи для договоров без платежей - скажем если он связывает таблицу договоров с таблицей платежей по LEFT JOIN - тогда запись по суммарной оплате будет, только через NVL(SUM(paym_sum),0) sum_paym_sum её считать, чтобы с NULL-ом по сумме не мучиться. Тогда можно применить INNER JOIN, и разбить условие - id_contract=id_contract отсавить в ON, а фильтр accum_sum > sum_paym_sum вынести в WHERE - зачастую так оно легче читается/воспринимается... Тут главное не забывать что "смешивание" условий в ON и WHERE "логично" работает лишь для INNER JOIN - для LEFT JOIN по хорошему только в ON нужно все условия писать, ну или городить огороды типа WHERE t1.f1 > t2.f2 OR (t1.f1 > 0 AND t2.f2 IS NULL)... Что и многословно, и чревато ошибками, да и на оптимизатор может негативно повлиять...

оффтоп
как-то 1 мой знакомый раскзаывал как он решил блеснуть английским и в переписке с заказчиками использовал не Yes
а почти Yep - но только он там 1 букву спутал написал Yap ( трепло
context.reverso.net)
долго потом удивлялся чего это чел с ним потом не хотел общаться
1 буква а как меняет смысл ;)