|
Мне нужно сделать автоматизацию отчетов, и для этого нужно срипт код. Нужно взять определенные columns из разных tables и с join'ить их. Как я понял для этого, надо использовать функция (Case when then else). Если кто автоматизировал данные в Excel прошу подсказать.
Колонны таблиц: select ssudnik.qty_ovd_days_loan, ssudnik.qty_ovd_days_interest, ssudnik.type_client, ssudnik.segment, loan_kzt, ssudnik.period_report, portfolio.id_segment, portfolio.cards_portfolio
пример кода автоматизации бд:
WITH main AS (
SELECT s.type_client,
CASE WHEN s.type_client = 'ЮЛ' THEN g.segment ELSE 'ФЛ' END AS segment,
CASE WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) <= 0 THEN 'Без просрочки'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 1 and 30 THEN 'Просрочка от 1 до 30 дней'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 31 and 60 THEN 'Просрочка от 31 до 60 дней,'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 61 and 90 THEN 'Просрочка от 61 до 90 дней,'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 91 and 120 THEN 'Просрочка от 91 до 120 дней,'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 121 and 150 THEN 'Просрочка от 121 до 150 дней'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 151 and 180 THEN 'Просрочка от 151 до 180 дней'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) > 180 THEN 'Просрочка от 180 и более дней'
END AS dpd,
sum(CASE WHEN (CASE WHEN s.qty_ovd_days_loan>=s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest end) >= 1 THEN s.loan_kzt ELSE 0 END) AS dpd_1,
sum(CASE WHEN (CASE WHEN s.qty_ovd_days_loan>=s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest end) >= 30 THEN s.loan_kzt ELSE 0 END) AS dpd_30,
sum(CASE WHEN (CASE WHEN s.qty_ovd_days_loan>=s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest end) >= 60 THEN s.loan_kzt ELSE 0 END) AS dpd_60,
sum(CASE WHEN (CASE WHEN s.qty_ovd_days_loan>=s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest end) >= 90 THEN s.loan_kzt ELSE 0 END) AS dpd_90,
s.period_report,
sum(s.loan_kzt) AS od_tng
FROM portfolio.ssudnik s
LEFT JOIN portfolio.id_segment g ON g."Id_segment" = s.segment
WHERE s.period_report >= '01.01.2018'
GROUP BY s.type_client,
CASE WHEN s.type_client = 'ЮЛ' THEN g.segment ELSE 'ФЛ' END,
CASE WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) <= 0 THEN 'Без просрочки'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 1 and 30 THEN 'Просрочка от 1 до 30 дней'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 31 and 60 THEN 'Просрочка от 31 до 60 дней,'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 61 and 90 THEN 'Просрочка от 61 до 90 дней,'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 91 and 120 THEN 'Просрочка от 91 до 120 дней,'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 121 and 150 THEN 'Просрочка от 121 до 150 дней'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) BETWEEN 151 and 180 THEN 'Просрочка от 151 до 180 дней'
WHEN (CASE WHEN s.qty_ovd_days_loan >= s.qty_ovd_days_interest THEN s.qty_ovd_days_loan ELSE s.qty_ovd_days_interest END) > 180 THEN 'Просрочка от 180 и более дней'
END,
s.period_report
UNION ALL
SELECT 'ФЛ' AS type_client,
'ФЛ' AS segment,
CASE WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) <= 0 THEN 'Без просрочки'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 1 and 30 THEN 'Просрочка от 1 до 30 дней'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 31 and 60 THEN 'Просрочка от 31 до 60 дней,'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 61 and 90 THEN 'Просрочка от 61 до 90 дней,'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 91 and 120 THEN 'Просрочка от 91 до 120 дней,'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 121 and 150 THEN 'Просрочка от 121 до 150 дней'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 151 and 180 THEN 'Просрочка от 151 до 180 дней'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) > 180 THEN 'Просрочка от 180 и более дней'
END AS dpd,
sum(CASE WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) >= 1 THEN c.loan_kzt + c."total_ovd_kzt" ELSE 0 END) AS dpd_1,
sum(CASE WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) >= 30 THEN c.loan_kzt + c."total_ovd_kzt" ELSE 0 END) AS dpd_30,
sum(CASE WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) >= 60 THEN c.loan_kzt + c."total_ovd_kzt" ELSE 0 END) AS dpd_60,
sum(CASE WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) >= 90 THEN c.loan_kzt + c."total_ovd_kzt" ELSE 0 END) AS dpd_90,
c.report_date AS period_report,
sum(c.loan_kzt + c."total_ovd_kzt") AS od_tng
FROM portfolio.cards_potfolio c
WHERE c.report_date >= '01.01.2018'
GROUP BY CASE WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) <= 0 THEN 'Без просрочки'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 1 and 30 THEN 'Просрочка от 1 до 30 дней'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 31 and 60 THEN 'Просрочка от 31 до 60 дней,'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 61 and 90 THEN 'Просрочка от 61 до 90 дней,'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 91 and 120 THEN 'Просрочка от 91 до 120 дней,'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 121 and 150 THEN 'Просрочка от 121 до 150 дней'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) BETWEEN 151 and 180 THEN 'Просрочка от 151 до 180 дней'
WHEN (CASE WHEN c."qnty_ovd_loan_days" >= c."qnty_ovd_intrst_days" THEN c."qnty_ovd_loan_days" ELSE c."qnty_ovd_intrst_days" END) > 180 THEN 'Просрочка от 180 и более дней'
END,
c.report_date
)
SELECT m.type_client AS "Категория клиента",
m.segment AS "Сегмент",
m.dpd AS "Категория",
sum(m.dpd_1) AS "Итого кредиты с просрочкой 1+",
sum(m.dpd_30) AS "Итого кредиты с просрочкой 30+",
sum(m.dpd_60) AS "Итого кредиты с просрочкой 60+",
sum(m.dpd_90) AS "Итого кредиты с просрочкой 90+",
m.period_report AS "Отчетная дата",
sum(m.od_tng) AS "ОД"
FROM main m
GROUP BY m.type_client,
m.segment,
m.dpd,
m.period_report;
СВОДная таблица должна выйти в Excel примерно так:
|
