Miravild

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

Таблица контактов (показан пример для одного долга).

CONTACT

Id_debt	Uploaded_date		Contact_date	Contact_result			Кумулятивная сумма платежей на дату контакта
123454	10.06.2014		12.06.2014	не ответили			0
123454	10.06.2014		17.06.2014	бросили трубку			0
123454	10.06.2014		21.06.2014	отказ от оплаты			0
123454	10.06.2014		25.06.2014	обещание			0
123454	10.06.2014		07.07.2014	не ответили			0
123454	10.06.2014		14.07.2014	бросили трубку			10000
123454	10.06.2014		18.07.2014	отказ от оплаты			10000
123454	10.06.2014		27.07.2014	не ответили			10000
123454	10.06.2014		03.08.2014	не ответили			10000
123454	10.06.2014		11.08.2014	не ответили			10000
123454	10.06.2014		22.08.2014	не ответили			15000
123454	10.06.2014		27.08.2014	не ответили			15000
123454	10.06.2014		03.09.2014	не ответили			15000
123454	10.06.2014		13.09.2014	не ответили			15000

Таблица платежей по долгу (показан пример для одного долга)

PAYMENT

Id_debt		Pay_date	Pay_sum
123454		11.07.2014	10000
123454		15.08.2014	5000

Создаём необходимые таблицы

CREATE TABLE "PAYMENT"
("ID_DEBT" INTEGER,
"PAY_DATE" DATE,
"PAY_SUM" NUMBER
) TABLESPACE MIRAVILD_DATA;
/

COMMENT ON COLUMN "PAYMENT"."ID_DEBT" IS 'Идентификатор долга';
COMMENT ON COLUMN "PAYMENT"."PAY_DATE" IS 'Дата платежа';
COMMENT ON COLUMN "PAYMENT"."PAY_SUM" IS 'Сумма платежа';
COMMENT ON TABLE "PAYMENT" IS 'Платёж по долгу';
/

ALTER TABLE "PAYMENT" MODIFY ("ID_DEBT" CONSTRAINT PAYMENT_ID_DEBT_NN NOT NULL ENABLE);
ALTER TABLE "PAYMENT" MODIFY ("PAY_DATE" CONSTRAINT PAYMENT_PAY_DATE_NN NOT NULL ENABLE);
ALTER TABLE "PAYMENT" MODIFY ("PAY_SUM" CONSTRAINT PAYMENT_PAY_SUM_NN NOT NULL ENABLE);
/

---

CREATE TABLE "CONTACT"
("ID_DEBT" INTEGER,
"UPLOADED_DATE" DATE,
"CONTACT_DATE" DATE,
"CONTACT_RESULT" VARCHAR2(32)
) TABLESPACE MIRAVILD_DATA;
/
COMMENT ON COLUMN "CONTACT"."ID_DEBT" IS 'Идентификатор долга';
COMMENT ON COLUMN "CONTACT"."UPLOADED_DATE" IS 'Дата поступления в работу';
COMMENT ON COLUMN "CONTACT"."CONTACT_DATE" IS 'Дата контакта';
COMMENT ON COLUMN "CONTACT"."CONTACT_RESULT" IS 'Результат контакта';
COMMENT ON TABLE "CONTACT" IS 'Контакт по долгу';
/
ALTER TABLE "CONTACT" MODIFY ("ID_DEBT" CONSTRAINT CONTACT_ID_DEBT_NN NOT NULL ENABLE);
ALTER TABLE "CONTACT" MODIFY ("UPLOADED_DATE" CONSTRAINT CONTACT_UPLOADED_DATE_NN NOT NULL ENABLE);
ALTER TABLE "CONTACT" MODIFY ("CONTACT_DATE" CONSTRAINT CONTACT_DATE_NN NOT NULL ENABLE);
ALTER TABLE "CONTACT" MODIFY ("CONTACT_RESULT" CONSTRAINT CONTACT_RESULT_NN NOT NULL ENABLE);
/

---

Добавляем тестовые данные

INSERT INTO PAYMENT P
(P.ID_DEBT,
P.PAY_DATE,
P.PAY_SUM)
VALUES
(123454,
TO_DATE('11.07.2014', 'DD.MM.YYYY'),
10000);

INSERT INTO PAYMENT P
(P.ID_DEBT,
P.PAY_DATE,
P.PAY_SUM)
VALUES
(123454,
TO_DATE('15.08.2014', 'DD.MM.YYYY'),
5000);

COMMIT WORK;
/

Варианты запросов

SELECT c.id_debt,
c.uploaded_date,
c.contact_date,
c.contact_result,
(SELECT nvl(SUM(p.pay_sum), 0)
FROM miravild.payment p
WHERE p.id_debt = c.id_debt
AND p.pay_date <= c.contact_date) AS cumulative_total
FROM miravild.contact c;
/

SELECT c.id_debt,
c.uploaded_date,
c.contact_date,
c.contact_result,
nvl(SUM(p.pay_sum), 0) AS cumulative_total
FROM miravild.contact c
LEFT JOIN miravild.payment p
ON p.id_debt = c.id_debt
AND p.pay_date <= c.contact_date
GROUP BY c.id_debt, c.uploaded_date, c.contact_date, c.contact_result
ORDER BY c.contact_date ASC;
/

SELECT DISTINCT c.id_debt,
c.uploaded_date,
c.contact_date,
c.contact_result,
nvl(SUM(p.pay_sum) over(PARTITION BY c.id_debt,
c.uploaded_date,
c.contact_date,
c.contact_result),
0) AS cumulative_total
FROM contact c
LEFT JOIN payment p
ON p.id_debt = c.id_debt
AND p.pay_date <= c.contact_date
ORDER BY c.contact_date ASC;
/

Скрипт

Tags: Oracle, SQL