Написать запрос, показывающий, сколько долгов было в каждом месяце 2014 года в работе по договору (долг считается в работе в данном месяце если по договору мы имеем право работать хотя бы один день данного месяца).
Создадим необходимые объекты (таблицу, ограничения, индексы):
CREATE TABLE "DEBT"
(
"ID_DEBT" INTEGER,
"ID_PORTFOLIO" INTEGER,
"ID_CLIENT" INTEGER,
"SIGN_DATE" DATE,
"END_DATE" DATE,
"OUTSTANDING_SUM" NUMBER,
"DPD" INTEGER
) NOLOGGING TABLESPACE MIRAVILD_DATA;
COMMENT ON TABLE "DEBT" IS 'Долг';
COMMENT ON COLUMN "DEBT"."ID_DEBT" IS 'Идентификатор долга';
COMMENT ON COLUMN "DEBT"."ID_PORTFOLIO" IS 'Идентификатор портфеля';
COMMENT ON COLUMN "DEBT"."ID_CLIENT" IS 'Идентификатор клиента';
COMMENT ON COLUMN "DEBT"."SIGN_DATE" IS 'Дата подписания договора по долгу';
COMMENT ON COLUMN "DEBT"."END_DATE" IS 'Дата окончания работы с долгом по договору';
COMMENT ON COLUMN "DEBT"."OUTSTANDING_SUM" IS 'Сумма ко взысканию';
COMMENT ON COLUMN "DEBT"."DPD" IS 'Количество дней просрочки';
CREATE UNIQUE INDEX "U_IDX_DEBT" ON "DEBT" ("ID_DEBT") TABLESPACE MIRAVILD_INDEX;
ALTER TABLE "DEBT" ADD CONSTRAINT "PK_DEBT" PRIMARY KEY ("ID_DEBT")
USING INDEX "U_IDX_DEBT" ENABLE VALIDATE;
ALTER TABLE "DEBT" MODIFY ("ID_PORTFOLIO" CONSTRAINT NN_DEBT_ID_PORTFOLIO NOT NULL ENABLE);
ALTER TABLE "DEBT" MODIFY ("ID_CLIENT" CONSTRAINT NN_DEBT_ID_CLIENT NOT NULL ENABLE);
ALTER TABLE "DEBT" MODIFY ("SIGN_DATE" CONSTRAINT NN_DEBT_SIGN_DATE NOT NULL ENABLE);
ALTER TABLE "DEBT" MODIFY ("END_DATE" CONSTRAINT NN_DEBT_END_DATE NOT NULL ENABLE);
/
Добавим тестовые данные:
INSERT INTO debt
(id_debt,
id_portfolio,
id_client,
sign_date,
end_date,
outstanding_sum,
dpd)
VALUES
(123,
1,
1,
to_date('01-04-2014', 'dd-mm-yyyy'),
to_date('28-12-2014', 'dd-mm-yyyy'),
500000,
234);
INSERT INTO debt
(id_debt,
id_portfolio,
id_client,
sign_date,
end_date,
outstanding_sum,
dpd)
VALUES
(345,
2,
2,
to_date('11-10-2013', 'dd-mm-yyyy'),
to_date('09-06-2014', 'dd-mm-yyyy'),
12000,
30);
INSERT INTO debt
(id_debt,
id_portfolio,
id_client,
sign_date,
end_date,
outstanding_sum,
dpd)
VALUES
(346,
2,
2,
to_date('11-12-2013', 'dd-mm-yyyy'),
to_date('09-06-2014', 'dd-mm-yyyy'),
50000,
45);
INSERT INTO debt
(id_debt,
id_portfolio,
id_client,
sign_date,
end_date,
outstanding_sum,
dpd)
VALUES
(347,
3,
2,
to_date('11-12-2012', 'dd-mm-yyyy'),
to_date('09-02-2013', 'dd-mm-yyyy'),
35000,
67);
INSERT INTO debt
(id_debt,
id_portfolio,
id_client,
sign_date,
end_date,
outstanding_sum,
dpd)
VALUES
(348,
4,
2,
to_date('01-02-2012', 'dd-mm-yyyy'),
to_date('09-06-2013', 'dd-mm-yyyy'),
35000,
67);
INSERT INTO debt
(id_debt,
id_portfolio,
id_client,
sign_date,
end_date,
outstanding_sum,
dpd)
VALUES
(349,
4,
2,
to_date('01-02-2012', 'dd-mm-yyyy'),
to_date('09-06-2013', 'dd-mm-yyyy'),
35000,
67);
INSERT INTO debt
(id_debt,
id_portfolio,
id_client,
sign_date,
end_date,
outstanding_sum,
dpd)
VALUES
(350,
4,
2,
to_date('01-01-2014', 'dd-mm-yyyy'),
to_date('16-01-2014', 'dd-mm-yyyy'),
35000,
67);
INSERT INTO debt
(id_debt,
id_portfolio,
id_client,
sign_date,
end_date,
outstanding_sum,
dpd)
VALUES
(351,
4,
2,
to_date('28-01-2014', 'dd-mm-yyyy'),
to_date('03-02-2014', 'dd-mm-yyyy'),
35000,
67);
INSERT INTO debt
(id_debt,
id_portfolio,
id_client,
sign_date,
end_date,
outstanding_sum,
dpd)
VALUES
(123454,
4,
2,
to_date('01-02-2012', 'dd-mm-yyyy'),
to_date('09-06-2015', 'dd-mm-yyyy'),
35000,
67);
COMMIT WORK;
/
Вариант запроса:
SELECT to_char(t.month, 'MONTH YYYY') as "Месяц Год",
(SELECT COUNT(*)
FROM debt d
WHERE (((d.sign_date BETWEEN t.month AND last_day(t.month)) OR
(d.end_date BETWEEN t.month AND last_day(t.month))) OR
((d.sign_date <= t.month AND d.end_date >= last_day(t.month)) OR
(d.sign_date >= t.month AND d.end_date >= last_day(t.month))))) AS "Кол-во долгов"
FROM (SELECT LEVEL AS number_month,
add_months(to_date('01.01.2014', 'dd.mm.yyyy'), LEVEL - 1) AS MONTH,
to_char(to_date(LEVEL, 'MM'),
'MONTH',
'NLS_DATE_LANGUAGE=RUSSIAN') AS mm
FROM dual
CONNECT BY LEVEL <= 12) t;
/
Результат запроса:
Месяц Год Кол-во долгов ЯНВАРЬ 2014 6 ФЕВРАЛЬ 2014 5 МАРТ 2014 4 АПРЕЛЬ 2014 4 МАЙ 2014 4 ИЮНЬ 2014 4 ИЮЛЬ 2014 2 АВГУСТ 2014 2 СЕНТЯБРЬ 2014 2 ОКТЯБРЬ 2014 2 НОЯБРЬ 2014 2 ДЕКАБРЬ 2014 2
P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776