Написать скрипты на любом известном языке SQL, чтобы получить список кредитов, которые на момент расчета имеют непогашенную задолженность, и рассчитать для каждого такого кредита:
- Общую (накопленную) сумму просроченного долга непогашенную (не выплаченную) к моменту расчета.
- Дату начала текущей (последней) просрочки. Под датой начала просрочки, в данной задаче понимается первая дата непрерывного периода, в котором общая сумма просроченного непогашенного долга > 0.Учесть, что периодов просрочек может быть несколько.
- Кол-во дней текущей просрочки.
Исходные данные:
Таблица PDCL – содержит информацию о выносах на просрочку неоплаченных сумм по кредиту и о погашениях просроченного долга.
Структура:
Date | Дата |
Customer | Номер клиента |
Deal | Номер кредита |
Currency | Валюта кредита |
Sum | сумма, вынесенная на просрочку («+») или выплаченная («-«) |
Пример:
Date | Customer | Deal | Currency | Sum |
12.12.2009 | 111110 | 111111 | RUR | 12000 |
25.12.2009 | 111110 | 111111 | RUR | 5000 |
12.12.2009 | 111110 | 122222 | RUR | 10000 |
12.01.2010 | 111110 | 111111 | RUR | -10100 |
20.11.2009 | 220000 | 222221 | RUR | 25000 |
20.12.2009 | 220000 | 222221 | RUR | 20000 |
31.12.2009 | 220001 | 222221 | RUR | -10000 |
29.12.2009 | 111110 | 122222 | RUR | -10000 |
27.11.2009 | 220001 | 222221 | RUR | -30000 |
Один клиент может иметь несколько кредитов. По кредиту выплаты могу совершать созаемщики. Если клиент, имеющий кредит, пропускает очередную выплату по графику, у него возникает просрочка. В таблице PDCL при этом появляется соответствующая запись, где Sum – невыплаченная сумма (с положительным знаком). Если, затем, клиент совершает выплату (полную сумму или ее часть) появляется новая запись, где Sum – выплаченная сумма (со знаком «-»). Следует учесть, что выплата клиента не обязательно полностью гасит накопленный долг, она может составлять лишь часть долга.
В приведенном примере по кредиту 111111 имеется невыплаченный просроченный долг на сумму 6900руб. (12000 + 5000 — 10100), дата начала текущей просрочки 12.12.2009.
Создаём таблицу и вносим данные:
CREATE TABLE "PDCL" ( "DT" DATE, "CUSTOMER" VARCHAR2(8 CHAR), "DEAL" VARCHAR2(8 CHAR), "CURRENCY" VARCHAR2(4 CHAR), "SUM" NUMBER ); / INSERT INTO PDCL (DT, CUSTOMER, DEAL, CURRENCY, SUM) VALUES (TO_DATE('12.12.2009', 'dd.mm.yyyy'), '111110', '111111', 'RUR', TO_NUMBER(12000)); INSERT INTO PDCL (DT, CUSTOMER, DEAL, CURRENCY, SUM) VALUES (TO_DATE('25.12.2009', 'dd.mm.yyyy'), '111110', '111111', 'RUR', TO_NUMBER(5000)); INSERT INTO PDCL (DT, CUSTOMER, DEAL, CURRENCY, SUM) VALUES (TO_DATE('12.12.2009', 'dd.mm.yyyy'), '111110', '122222', 'RUR', TO_NUMBER(10000)); INSERT INTO PDCL (DT, CUSTOMER, DEAL, CURRENCY, SUM) VALUES (TO_DATE('12.01.2010', 'dd.mm.yyyy'), '111110', '111111', 'RUR', TO_NUMBER(-10100)); INSERT INTO PDCL (DT, CUSTOMER, DEAL, CURRENCY, SUM) VALUES (TO_DATE('20.11.2009', 'dd.mm.yyyy'), '220000', '222221', 'RUR', TO_NUMBER(25000)); INSERT INTO PDCL (DT, CUSTOMER, DEAL, CURRENCY, SUM) VALUES (TO_DATE('20.12.2009', 'dd.mm.yyyy'), '220000', '222221', 'RUR', TO_NUMBER(20000)); INSERT INTO PDCL (DT, CUSTOMER, DEAL, CURRENCY, SUM) VALUES (TO_DATE('31.12.2009', 'dd.mm.yyyy'), '220001', '222221', 'RUR', TO_NUMBER(-10000)); INSERT INTO PDCL (DT, CUSTOMER, DEAL, CURRENCY, SUM) VALUES (TO_DATE('29.12.2009', 'dd.mm.yyyy'), '111110', '122222', 'RUR', TO_NUMBER(-10000)); INSERT INTO PDCL (DT, CUSTOMER, DEAL, CURRENCY, SUM) VALUES (TO_DATE('27.11.2009', 'dd.mm.yyyy'), '220001', '222221', 'RUR', TO_NUMBER(-30000)); COMMIT WORK; /
Вариант запроса:
SELECT P.CUSTOMER, P.DEAL, P.TOTAL_AMOUNT, -- 1. Общая (накопленная) сумма просрочки P.DELAY_START_DATE, -- 2. Дата начала текущей (последней) просрочки (TRUNC(SYSDATE) - P.DELAY_START_DATE) AS Number_days_delay -- 3. Кол-во дней текущей просрочки FROM (SELECT P.DT, P.CUSTOMER, P.DEAL, P.SUM, COALESCE(SUM(P.SUM) OVER(PARTITION BY P.CUSTOMER, P.DEAL ORDER BY P.DT ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS TOTAL_AMOUNT, RANK() OVER(PARTITION BY P.CUSTOMER, P.DEAL ORDER BY P.DT DESC) AS R, MIN(P.DT) KEEP(DENSE_RANK FIRST ORDER BY P.DT) OVER(PARTITION BY P.CUSTOMER, P.DEAL) AS DELAY_START_DATE FROM PDCL P WHERE P.DT <= TRUNC(SYSDATE)) P WHERE P.R = 1 AND P.TOTAL_AMOUNT > 0; /
Tags: SQL;Oracle