https://pastein.ru/t/3AA
Загрузка данных
-- =====================================================
-- ЗАПРОС 1: NPO
-- =====================================================
WITH filtered_sharers AS (
SELECT
s.id AS id,
ps.name AS pensionName,
cc.name AS iv_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.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 b.account_id = ra.account_id
GROUP BY ra.pensionName, ra.iv_name
),
ops_npo 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_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
),
npo_detail AS (
SELECT
bd.pensionName,
bd.iv_name,
bd.balance_begin_sum,
COALESCE(o.deposit_enrollment, 0) AS deposit_enrollment,
COALESCE(o.npo_pension_contrib_return, 0) AS npo_pension_contrib_return,
COALESCE(o.payment_pension, 0) AS payment_pension,
COALESCE(o.payment_terminated, 0) AS payment_terminated,
COALESCE(o.succesor_sum, 0) AS succesor_sum,
COALESCE(o.npo_returns, 0) AS npo_returns,
COALESCE(o.payment_pension, 0)
+ COALESCE(o.payment_terminated, 0)
+ COALESCE(o.succesor_sum, 0)
- COALESCE(o.npo_returns, 0) AS total,
COALESCE(o.transfer_to_insured_fund_reserve, 0) AS transfer_to_insured_fund_reserve,
COALESCE(o.transfer_from_insured_fund_reserve, 0) AS transfer_from_insured_fund_reserve,
COALESCE(o.npo_target_contributions, 0) AS npo_target_contributions,
COALESCE(o.transfer_to_insured_fund_reserve_ops, 0) AS transfer_to_insured_fund_reserve_ops,
COALESCE(o.pension_investment_revenue_enrollment_pr, 0) AS pension_investment_revenue_enrollment_pr,
bd.balance_end_sum
FROM balance_data bd
LEFT JOIN ops_npo o
ON o.pensionName = bd.pensionName AND o.iv_name = bd.iv_name
WHERE bd.iv_name IN ('Инвестиционный', 'Страховой')
),
npo_total 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 npo_detail
GROUP BY iv_name
)
SELECT * FROM npo_detail
UNION ALL
SELECT * FROM npo_total
WHERE iv_name IN ('Инвестиционный', 'Страховой');
-- =====================================================
-- ЗАПРОС 2: PDS
-- =====================================================
WITH filtered_sharers AS (
SELECT
s.id AS id,
ps.name AS pensionName,
cc.name AS iv_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.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 b.account_id = ra.account_id
GROUP BY ra.pensionName, ra.iv_name
),
ops_pds 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 = 'PDS_GOS_ENROLLMENT'
THEN t.value END) AS pds_gos_enrollment,
SUM(CASE WHEN ot.code = '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
),
pds_detail AS (
SELECT
bd.pensionName,
bd.iv_name,
bd.balance_begin_sum,
COALESCE(o.pds_enrollment, 0) AS pds_enrollment,
COALESCE(o.pds_deposit_enrollment, 0) AS pds_deposit_enrollment,
COALESCE(o.pds_deposit_enrollment_for_employer, 0) AS pds_deposit_enrollment_for_employer,
COALESCE(o.pds_gos_enrollment, 0) AS pds_gos_enrollment,
COALESCE(o.pds_savings_contrib_return, 0) AS pds_savings_contrib_return,
COALESCE(o.pds_ev_payment, 0) AS pds_ev_payment,
COALESCE(o.pds_redemption_payment, 0) AS pds_redemption_payment,
COALESCE(o.pds_successor_payment, 0) AS pds_successor_payment,
COALESCE(o.pds_pension_payment, 0) AS pds_pension_payment,
COALESCE(o.pds_redemption_payment_to_np, 0) AS pds_redemption_payment_to_np,
COALESCE(o.pds_returns, 0) AS pds_returns,
COALESCE(o.transfer_to_insured_fund_reserve, 0) AS transfer_to_insured_fund_reserve,
COALESCE(o.transfer_from_insured_fund_reserve, 0) AS transfer_from_insured_fund_reserve,
COALESCE(o.financial_result, 0) AS financial_result,
bd.balance_end_sum
FROM balance_data bd
LEFT JOIN ops_pds o
ON o.pensionName = bd.pensionName AND o.iv_name = bd.iv_name
WHERE bd.iv_name IN ('Инвестиционный', 'Страховой')
),
pds_total 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 pds_detail
GROUP BY iv_name
)
SELECT * FROM pds_detail
UNION ALL
SELECT * FROM pds_total
WHERE iv_name IN ('Инвестиционный', 'Страховой');
