https://pastein.ru/t/lAA
Загрузка данных
-- Оптимизированная версия с объединением дубликатов и упрощением логики
--------------------- NPO
WITH filtered_sharers AS (
SELECT
s.id AS id,
ps.name as pensionName,
cc.name
FROM ourpension.sharer s
JOIN ourpension.contract c ON c.id = s.contract_id
JOIN ourpension.pension_scheme ps ON ps.id = s.scheme_id
JOIN ourpension.sharer_class_history sch ON sch.sharer_id = s.id AND sch.end_date IS NULL
JOIN ourpension.contract_classes cc ON cc.id = sch.class_id
WHERE c.service_type = 'NPO'
),
relevant_accounts AS (
SELECT
a.id as account_id,
fs.pensionName,
fs.name as iv_name
FROM filtered_sharers fs
JOIN back_office.account a ON a.owner_sharer_id = fs.id
JOIN back_office.account_type at ON a.account_type_id = at.id
JOIN back_office.account_type_group atg ON at.account_type_group_id = atg.id
WHERE atg.mnemonics != 'ВЗ'
),
combined_eps AS (
SELECT
ra.pensionName,
ra.iv_name,
e.code,
SUM(t.value) AS sum_oper
FROM relevant_accounts ra
JOIN back_office."transaction" t ON t.credit_account_id = ra.account_id
JOIN back_office.operation o ON o.id = t.operation_id
JOIN back_office.eps_transaction_source s ON o.id = s.operation_id
JOIN back_office.eps_transaction et ON et.eps_transaction_source_id = s.id
JOIN ourpension.eps e ON e.id = et.eps_id
WHERE o.effective_date BETWEEN '2026-01-01' AND '2026-06-01'
AND e.code IN (
'172053', '172155', '172156', '172157', '172158', -- qualification codes
'172091', '172149', '172150', '172151', '172152' -- distribution codes
)
GROUP BY ra.pensionName, ra.iv_name, e.code
),
balance_data AS (
SELECT
ra.pensionName,
ra.iv_name,
SUM(CASE WHEN '2026-05-01' BETWEEN b.start_date AND b.end_date THEN b.total_credit - b.total_debit END) AS balance_begin_sum,
SUM(CASE WHEN '2026-06-01' BETWEEN b.start_date AND b.end_date THEN b.total_credit - b.total_debit END) AS balance_end_sum
FROM relevant_accounts ra
JOIN back_office.balance b ON ra.account_id = b.account_id
GROUP BY ra.pensionName, ra.iv_name
),
operation_summary_direct AS (
SELECT
ra.pensionName,
ra.iv_name,
SUM(CASE WHEN ot.code = 'DEPOSIT_ENROLLMENT' THEN t.value END) AS deposit_enrollment,
SUM(CASE WHEN ot.code = 'NPO_PENSION_CONTRIB_RETURN' THEN t.value END) AS npo_pension_contrib_return,
SUM(CASE WHEN ot.code = 'PAYMENT_PENSION' THEN t.value END) AS payment_pension,
SUM(CASE WHEN ot.code IN ('PAYMENT_TERMINATED', 'NPO_NDFL_REDEMPTION') THEN t.value END) AS payment_terminated,
SUM(CASE WHEN ot.code IN ('PAYMENT_INHERITED', 'NPO_NDFL_SUCCESSOR') THEN t.value END) AS succesor_sum,
SUM(CASE WHEN ot.code IN (
'NPO_RETURN_NDFL_NONSTATE_PENSION', 'NPO_RETURN_REDEMPTION_SUM', 'NPO_RETURN_NDFL_REDEMPTION',
'NPO_RETURN_SUCCESSOR_PAYMENT', 'NPO_RETURN_NDFL_SUCCESSOR', 'NPO_RETURN_HEIRS_PAYMENT', 'NPO_RETURN_NDFL_HEIRS'
) THEN t.value END) AS npo_returns,
SUM(CASE WHEN ot.code = 'TRANSFER_TO_INSURED_FUND_RESERVE' THEN t.value END) AS transfer_to_insured_fund_reserve,
SUM(CASE WHEN ot.code = 'TRANSFER_TO_INSURED_FUND_RESERVE' THEN t.value END) AS transfer_to_insured_fund_reserve_rppo,
SUM(CASE WHEN ot.code = 'TRANSFER_FROM_INSURED_FUND_RESERVE' THEN t.value END) AS transfer_from_insured_fund_reserve,
SUM(CASE WHEN ot.code = 'NPO_TARGET_CONTRIBUTIONS' THEN t.value END) AS npo_target_contributions,
SUM(CASE WHEN ot.code = 'TRANSFER_TO_INSURED_FUND_RESERVE_OPS' THEN t.value END) AS transfer_to_insured_fund_reserve_ops,
SUM(CASE WHEN ot.code = 'PENSION_INVESTMENT_REVENUE_ENROLLMENT_PR' THEN t.value END) AS pension_investment_revenue_enrollment_pr
FROM relevant_accounts ra
JOIN back_office."transaction" t ON t.credit_account_id = ra.account_id
JOIN back_office.operation o ON o.id = t.operation_id
JOIN back_office.operation_type ot ON o.operation_type_id = ot.id
WHERE o.effective_date BETWEEN '2026-05-01' AND '2026-06-01'
AND ot.code IN (
'DEPOSIT_ENROLLMENT', 'NPO_PENSION_CONTRIB_RETURN', 'PAYMENT_PENSION', 'PAYMENT_TERMINATED',
'NPO_NDFL_REDEMPTION', 'NPO_NDFL_SUCCESSOR', 'PAYMENT_INHERITED', 'NPO_NDFL_HEIRS', 'NPO_RETURN_NDFL_NONSTATE_PENSION',
'NPO_RETURN_REDEMPTION_SUM', 'NPO_RETURN_NDFL_REDEMPTION', 'NPO_RETURN_SUCCESSOR_PAYMENT',
'NPO_RETURN_NDFL_SUCCESSOR', 'NPO_RETURN_HEIRS_PAYMENT', 'NPO_RETURN_NDFL_HEIRS',
'TRANSFER_TO_INSURED_FUND_RESERVE', 'TRANSFER_FROM_INSURED_FUND_RESERVE',
'NPO_TARGET_CONTRIBUTIONS', 'TRANSFER_TO_INSURED_FUND_RESERVE_OPS',
'PENSION_INVESTMENT_REVENUE_ENROLLMENT_PR'
)
GROUP BY ra.pensionName, ra.iv_name
),
total_by_iv_name AS (
SELECT
iv_name,
'Итого' AS pensionName,
SUM(balance_begin_sum) AS balance_begin_sum,
SUM(deposit_enrollment) AS deposit_enrollment,
SUM(npo_pension_contrib_return) AS npo_pension_contrib_return,
SUM(payment_pension) AS payment_pension,
SUM(payment_terminated) AS payment_terminated,
SUM(succesor_sum) AS succesor_sum,
SUM(npo_returns) AS npo_returns,
SUM(total) AS total,
SUM(transfer_to_insured_fund_reserve) AS transfer_to_insured_fund_reserve,
SUM(transfer_from_insured_fund_reserve) AS transfer_from_insured_fund_reserve,
SUM(npo_target_contributions) AS npo_target_contributions,
SUM(transfer_to_insured_fund_reserve_ops) AS transfer_to_insured_fund_reserve_ops,
SUM(pension_investment_revenue_enrollment_pr) AS pension_investment_revenue_enrollment_pr,
SUM(balance_end_sum) AS balance_end_sum
FROM (
SELECT
bb.iv_name,
bb.balance_begin_sum,
COALESCE(osd.deposit_enrollment, 0) AS deposit_enrollment,
COALESCE(osd.npo_pension_contrib_return, 0) AS npo_pension_contrib_return,
COALESCE(osd.payment_pension, 0) AS payment_pension,
COALESCE(osd.payment_terminated, 0) AS payment_terminated,
COALESCE(osd.succesor_sum, 0) AS succesor_sum,
COALESCE(osd.npo_returns, 0) AS npo_returns,
COALESCE(osd.payment_pension, 0) + COALESCE(osd.payment_terminated, 0) + COALESCE(osd.succesor_sum, 0) - COALESCE(osd.npo_returns, 0) AS total,
COALESCE(osd.transfer_to_insured_fund_reserve, 0) AS transfer_to_insured_fund_reserve,
COALESCE(osd.transfer_from_insured_fund_reserve, 0) AS transfer_from_insured_fund_reserve,
COALESCE(osd.npo_target_contributions, 0) AS npo_target_contributions,
COALESCE(osd.transfer_to_insured_fund_reserve_ops, 0) AS transfer_to_insured_fund_reserve_ops,
COALESCE(osd.pension_investment_revenue_enrollment_pr, 0) AS pension_investment_revenue_enrollment_pr,
be.balance_end_sum
FROM balance_data bb
LEFT JOIN operation_summary_direct osd ON osd.pensionName = bb.pensionName AND osd.iv_name = bb.iv_name
LEFT JOIN balance_data be ON be.pensionName = bb.pensionName AND be.iv_name = bb.iv_name
) subquery
GROUP BY iv_name
)
SELECT
bb.pensionName,
bb.iv_name,
bb.balance_begin_sum,
COALESCE(osd.deposit_enrollment, 0) AS deposit_enrollment,
COALESCE(osd.npo_pension_contrib_return, 0) AS npo_pension_contrib_return,
COALESCE(osd.payment_pension, 0) AS payment_pension,
COALESCE(osd.payment_terminated, 0) AS payment_terminated,
COALESCE(osd.succesor_sum, 0) AS succesor_sum,
COALESCE(osd.npo_returns, 0) AS npo_returns,
COALESCE(osd.payment_pension, 0) + COALESCE(osd.payment_terminated, 0) + COALESCE(osd.succesor_sum, 0) - COALESCE(osd.npo_returns, 0) AS total,
COALESCE(osd.transfer_to_insured_fund_reserve, 0) AS transfer_to_insured_fund_reserve,
COALESCE(osd.transfer_from_insured_fund_reserve, 0) AS transfer_from_insured_fund_reserve,
COALESCE(osd.npo_target_contributions, 0) AS npo_target_contributions,
COALESCE(osd.transfer_to_insured_fund_reserve_ops, 0) AS transfer_to_insured_fund_reserve_ops,
COALESCE(osd.pension_investment_revenue_enrollment_pr, 0) AS pension_investment_revenue_enrollment_pr,
be.balance_end_sum
FROM balance_data bb
LEFT JOIN operation_summary_direct osd ON osd.pensionName = bb.pensionName AND osd.iv_name = bb.iv_name
LEFT JOIN balance_data be ON be.pensionName = bb.pensionName AND be.iv_name = bb.iv_name
WHERE bb.iv_name IN ('Инвестиционный', 'Страховой')
UNION ALL
SELECT * FROM total_by_iv_name
WHERE iv_name IN ('Инвестиционный', 'Страховой');
--------------------- NPO
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------- PDS
WITH filtered_sharers AS (
SELECT
s.id AS id,
ps.name as pensionName,
cc.name
FROM ourpension.sharer s
JOIN ourpension.contract c ON c.id = s.contract_id
JOIN ourpension.pension_scheme ps ON ps.id = s.scheme_id
JOIN ourpension.sharer_class_history sch ON sch.sharer_id = s.id AND sch.end_date IS NULL
JOIN ourpension.contract_classes cc ON cc.id = sch.class_id
WHERE c.service_type = 'PDS'
),
relevant_accounts AS (
SELECT
a.id as account_id,
fs.pensionName,
fs.name as iv_name
FROM filtered_sharers fs
JOIN back_office.account a ON a.owner_sharer_id = fs.id
JOIN back_office.account_type at ON a.account_type_id = at.id
JOIN back_office.account_type_group atg ON at.account_type_group_id = atg.id
WHERE atg.mnemonics != 'ВЗ'
),
balance_data AS (
SELECT
ra.pensionName,
ra.iv_name,
SUM(CASE WHEN '2026-05-01' BETWEEN b.start_date AND b.end_date THEN b.total_credit - b.total_debit END) AS balance_begin_sum,
SUM(CASE WHEN '2026-06-01' BETWEEN b.start_date AND b.end_date THEN b.total_credit - b.total_debit END) AS balance_end_sum
FROM relevant_accounts ra
JOIN back_office.balance b ON ra.account_id = b.account_id
GROUP BY ra.pensionName, ra.iv_name
),
operation_summary_direct AS (
SELECT
ra.pensionName,
ra.iv_name,
SUM(CASE WHEN ot.code = 'PDS_OPS_ENROLLMENT_PDS' THEN t.value END) AS pds_enrollment,
SUM(CASE WHEN ot.code = 'DS_DEPOSIT_ENROLLMENT' THEN t.value END) AS pds_deposit_enrollment,
SUM(CASE WHEN ot.code = 'PDS_DEPOSIT_ENROLLMENT_FOR_EMPLOYER' THEN t.value END) AS pds_deposit_enrollment_for_employer,
SUM(CASE WHEN ot.code IN ('PDS_GOS_ENROLLMENT') THEN t.value END) AS pds_gos_enrollment,
SUM(CASE WHEN ot.code IN ('PDS_SAVINGS_CONTRIB_RETURN') THEN t.value END) AS pds_savings_contrib_return,
SUM(CASE WHEN ot.code IN (
'PDS_EV_PAYMENT', 'PDS_NDFL_LUMP_SUM_PAYMENT'
) THEN t.value END) AS pds_ev_payment,
SUM(CASE WHEN ot.code IN ('PDS_REDEMPTION_PAYMENT', 'PDS_NDFL_REDEMPTION_PAYMENT') THEN t.value END) AS pds_redemption_payment,
SUM(CASE WHEN ot.code IN ('PDS_SUCCESSOR_PAYMENT', 'PDS_NDFL_SUCCESSOR_PAYMENT') THEN t.value END) AS pds_successor_payment,
SUM(CASE WHEN ot.code IN ('PDS_PENSION_PAYMENT', 'PDS_NDFL_PENSION_PAYMENT') THEN t.value END) AS pds_pension_payment,
SUM(CASE WHEN ot.code = 'PDS_REDEMPTION_PAYMENT_TO_NP' THEN t.value END) AS pds_redemption_payment_to_np,
SUM(CASE WHEN ot.code IN (
'PDS_RETURN_PAYMENT', 'PDS_RETURN_LUMP_SUM_PAYMENT', 'PDS_RETURN_NDFL_LUMP_SUM',
'PDS_RETURN_PERIODIC_PAYMENT', 'PDS_RETURN_NDFL_PERIODIC', 'PDS_RETURN_NDFL_REDEMPTION_LOSS',
'PDS_RETURN_REDEMPTION_LIFELONG', 'PDS_RETURN_SUCCESSOR_PAYMENT', 'PDS_RETURN_NDFL_SUCCESSOR',
'PDS_RETURN_REDEMPTION_SUM', 'PDS_RETURN_NDFL_REDEMPTION'
) THEN t.value END) AS pds_returns,
SUM(CASE WHEN ot.code = 'TRANSFER_TO_INSURED_FUND_RESERVE' THEN t.value END) AS transfer_to_insured_fund_reserve,
SUM(CASE WHEN ot.code = 'PDS_TRANSFER_FROM_INSURED_FUND_RESERVE' THEN t.value END) AS transfer_from_insured_fund_reserve,
SUM(CASE WHEN ot.code = 'PDS_PENSION_INVESTMENT_REVENUE_ENROLLMENT' THEN t.value END) AS financial_result
FROM relevant_accounts ra
JOIN back_office."transaction" t ON t.credit_account_id = ra.account_id
JOIN back_office.operation o ON o.id = t.operation_id
JOIN back_office.operation_type ot ON o.operation_type_id = ot.id
WHERE o.effective_date BETWEEN '2026-05-01' AND '2026-06-01'
AND ot.code IN (
'PDS_OPS_ENROLLMENT_PDS', 'DS_DEPOSIT_ENROLLMENT', 'PDS_DEPOSIT_ENROLLMENT_FOR_EMPLOYER',
'PDS_GOS_ENROLLMENT', 'PDS_SAVINGS_CONTRIB_RETURN', 'PDS_EV_PAYMENT', 'PDS_NDFL_LUMP_SUM_PAYMENT',
'PDS_REDEMPTION_PAYMENT', 'PDS_NDFL_REDEMPTION_PAYMENT', 'PDS_SUCCESSOR_PAYMENT',
'PDS_NDFL_SUCCESSOR_PAYMENT', 'PDS_PENSION_PAYMENT', 'PDS_NDFL_PENSION_PAYMENT',
'PDS_REDEMPTION_PAYMENT_TO_NP', 'PDS_RETURN_PAYMENT', 'PDS_RETURN_LUMP_SUM_PAYMENT',
'PDS_RETURN_NDFL_LUMP_SUM', 'PDS_RETURN_PERIODIC_PAYMENT', 'PDS_RETURN_NDFL_PERIODIC',
'PDS_RETURN_NDFL_REDEMPTION_LOSS', 'PDS_RETURN_REDEMPTION_LIFELONG', 'PDS_RETURN_SUCCESSOR_PAYMENT',
'PDS_RETURN_NDFL_SUCCESSOR', 'PDS_RETURN_REDEMPTION_SUM', 'PDS_RETURN_NDFL_REDEMPTION',
'TRANSFER_TO_INSURED_FUND_RESERVE', 'PDS_TRANSFER_FROM_INSURED_FUND_RESERVE',
'PDS_PENSION_INVESTMENT_REVENUE_ENROLLMENT'
)
GROUP BY ra.pensionName, ra.iv_name
),
total_by_iv_name AS (
SELECT
iv_name,
'Итого' AS pensionName,
SUM(balance_begin_sum) AS balance_begin_sum,
SUM(pds_enrollment) AS pds_enrollment,
SUM(pds_deposit_enrollment) AS pds_deposit_enrollment,
SUM(pds_deposit_enrollment_for_employer) AS pds_deposit_enrollment_for_employer,
SUM(pds_gos_enrollment) AS pds_gos_enrollment,
SUM(pds_savings_contrib_return) AS pds_savings_contrib_return,
SUM(pds_ev_payment) AS pds_ev_payment,
SUM(pds_redemption_payment) AS pds_redemption_payment,
SUM(pds_successor_payment) AS pds_successor_payment,
SUM(pds_pension_payment) AS pds_pension_payment,
SUM(pds_redemption_payment_to_np) AS pds_redemption_payment_to_np,
SUM(pds_returns) AS pds_returns,
SUM(transfer_to_insured_fund_reserve) AS transfer_to_insured_fund_reserve,
SUM(transfer_from_insured_fund_reserve) AS transfer_from_insured_fund_reserve,
SUM(financial_result) AS financial_result,
SUM(balance_end_sum) AS balance_end_sum
FROM (
SELECT
bb.iv_name,
bb.balance_begin_sum,
COALESCE(osd.pds_enrollment, 0) AS pds_enrollment,
COALESCE(osd.pds_deposit_enrollment, 0) AS pds_deposit_enrollment,
COALESCE(osd.pds_deposit_enrollment_for_employer, 0) AS pds_deposit_enrollment_for_employer,
COALESCE(osd.pds_gos_enrollment, 0) AS pds_gos_enrollment,
COALESCE(osd.pds_savings_contrib_return, 0) AS pds_savings_contrib_return,
COALESCE(osd.pds_ev_payment, 0) AS pds_ev_payment,
COALESCE(osd.pds_redemption_payment, 0) AS pds_redemption_payment,
COALESCE(osd.pds_successor_payment, 0) AS pds_successor_payment,
COALESCE(osd.pds_pension_payment, 0) AS pds_pension_payment,
COALESCE(osd.pds_redemption_payment_to_np, 0) AS pds_redemption_payment_to_np,
COALESCE(osd.pds_returns, 0) AS pds_returns,
COALESCE(osd.transfer_to_insured_fund_reserve, 0) AS transfer_to_insured_fund_reserve,
COALESCE(osd.transfer_from_insured_fund_reserve, 0) AS transfer_from_insured_fund_reserve,
COALESCE(osd.financial_result, 0) AS financial_result,
be.balance_end_sum
FROM balance_data bb
LEFT JOIN operation_summary_direct osd
ON osd.pensionName = bb.pensionName AND osd.iv_name = bb.iv_name
LEFT JOIN balance_data be
ON be.pensionName = bb.pensionName AND be.iv_name = bb.iv_name
) subquery
GROUP BY iv_name
),
final_output AS (
SELECT
bb.pensionName,
bb.iv_name,
bb.balance_begin_sum,
COALESCE(osd.pds_enrollment, 0) AS pds_enrollment,
COALESCE(osd.pds_deposit_enrollment, 0) AS pds_deposit_enrollment,
COALESCE(osd.pds_deposit_enrollment_for_employer, 0) AS pds_deposit_enrollment_for_employer,
COALESCE(osd.pds_gos_enrollment, 0) AS pds_gos_enrollment,
COALESCE(osd.pds_savings_contrib_return, 0) AS pds_savings_contrib_return,
COALESCE(osd.pds_ev_payment, 0) AS pds_ev_payment,
COALESCE(osd.pds_redemption_payment, 0) AS pds_redemption_payment,
COALESCE(osd.pds_successor_payment, 0) AS pds_successor_payment,
COALESCE(osd.pds_pension_payment, 0) AS pds_pension_payment,
COALESCE(osd.pds_redemption_payment_to_np, 0) AS pds_redemption_payment_to_np,
COALESCE(osd.pds_returns, 0) AS pds_returns,
COALESCE(osd.transfer_to_insured_fund_reserve, 0) AS transfer_to_insured_fund_reserve,
COALESCE(osd.transfer_from_insured_fund_reserve, 0) AS transfer_from_insured_fund_reserve,
COALESCE(osd.financial_result, 0) AS financial_result,
be.balance_end_sum
FROM balance_data bb
LEFT JOIN operation_summary_direct osd
ON osd.pensionName = bb.pensionName AND osd.iv_name = bb.iv_name
LEFT JOIN balance_data be
ON be.pensionName = bb.pensionName AND be.iv_name = bb.iv_name
WHERE bb.iv_name IN ('Инвестиционный', 'Страховой')
)
SELECT * FROM final_output
UNION ALL
SELECT * FROM total_by_iv_name
WHERE iv_name IN ('Инвестиционный', 'Страховой');
--------------------- PDS
