Skip to content

Instantly share code, notes, and snippets.

@kovenko

kovenko/gist:2f2640b8143331641624ddddaa7096a9

Created April 21, 2020 18:00
Show Gist options
  • Save kovenko/2f2640b8143331641624ddddaa7096a9 to your computer and use it in GitHub Desktop.
Save kovenko/2f2640b8143331641624ddddaa7096a9 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
WITH cte_data1 AS (
SELECT
sf.dative AS sf_name,
mo.name AS mo_name,
vpd.documents_name AS document_name,
s.name AS status_name,
vpd.id AS volga_program_document_id
FROM volga_river_recovery.regional_programs rp
JOIN geo_tag_grammatical_cases sf ON sf.geo_tag_id = rp.geo_tag_id
JOIN volga_river_recovery.regional_programs_mun_areas rpma ON rpma.regional_program_id = rp.id AND rpma.deleted_at IS NULL
JOIN geo_tags mo ON mo.id = rpma.geo_tag_id
JOIN volga_river_recovery.volga_program_documents vpd ON vpd.municipal_area_id = rpma.id AND vpd.deleted_at IS NULL
JOIN statuses s ON s.id = vpd.execution_status_id --AND s.code <> :anotherSources --AND s.object_id = :objectId
WHERE rp.id = :regionalProgramId
), cte_data2 AS (
SELECT
descr.volga_program_document_id,
to_char(max(sc.created_at), 'DD.MM.YYYY') AS status_change
FROM volga_river_recovery.documents_execution_status_changes_rel descr
LEFT JOIN status_changes sc ON sc.id = descr.status_change_id
WHERE descr.volga_program_document_id IN (SELECT volga_program_document_id FROM cte_data1)
GROUP BY descr.volga_program_document_id
), cte_data3 AS (
SELECT
vfi.volga_program_document_id,
coalesce(sum(vfi.federal_budget_documents / 1000) FILTER(WHERE vfi.year = 2019), 0) AS federal_budget_documents_2019,
coalesce(sum(vfi.subject_documents / 1000) FILTER(WHERE vfi.year = 2019), 0) AS subject_budget_documents_2019,
coalesce(sum(vfi.municipal_budget_documents / 1000) FILTER(WHERE vfi.year = 2019), 0) AS municipal_budget_documents_2019,
coalesce(sum(vfi.extrabudgetary_resourses_documents / 1000) FILTER(WHERE vfi.year = 2019), 0) AS extrabudgetary_resourses_documents_2019,
coalesce(sum(vfi.federal_budget_works / 1000) FILTER(WHERE vfi.year = 2019), 0) AS federal_budget_works_2019,
coalesce(sum(vfi.subject_budget_works / 1000) FILTER(WHERE vfi.year = 2019), 0) AS subject_budget_works_2019,
coalesce(sum(vfi.municipal_budget_works / 1000) FILTER(WHERE vfi.year = 2019), 0) AS municipal_budget_works_2019,
coalesce(sum(vfi.extrabudgetary_resourses_works / 1000) FILTER(WHERE vfi.year = 2019), 0) AS extrabudgetary_resourses_works_2019,
coalesce(sum(vfi.federal_budget_documents / 1000) FILTER(WHERE vfi.year = 2020), 0) AS federal_budget_documents_2020,
coalesce(sum(vfi.subject_documents / 1000) FILTER(WHERE vfi.year = 2020), 0) AS subject_budget_documents_2020,
coalesce(sum(vfi.municipal_budget_documents / 1000) FILTER(WHERE vfi.year = 2020), 0) AS municipal_budget_documents_2020,
coalesce(sum(vfi.extrabudgetary_resourses_documents / 1000) FILTER(WHERE vfi.year = 2020), 0) AS extrabudgetary_resourses_documents_2020,
coalesce(sum(vfi.federal_budget_works / 1000) FILTER(WHERE vfi.year = 2020), 0) AS federal_budget_works_2020,
coalesce(sum(vfi.subject_budget_works / 1000) FILTER(WHERE vfi.year = 2020), 0) AS subject_budget_works_2020,
coalesce(sum(vfi.municipal_budget_works / 1000) FILTER(WHERE vfi.year = 2020), 0) AS municipal_budget_works_2020,
coalesce(sum(vfi.extrabudgetary_resourses_works / 1000) FILTER(WHERE vfi.year = 2020), 0) AS extrabudgetary_resourses_works_2020,
coalesce(sum(vfi.federal_budget_documents / 1000) FILTER(WHERE vfi.year = 2021), 0) AS federal_budget_documents_2021,
coalesce(sum(vfi.subject_documents / 1000) FILTER(WHERE vfi.year = 2021), 0) AS subject_budget_documents_2021,
coalesce(sum(vfi.municipal_budget_documents / 1000) FILTER(WHERE vfi.year = 2021), 0) AS municipal_budget_documents_2021,
coalesce(sum(vfi.extrabudgetary_resourses_documents / 1000) FILTER(WHERE vfi.year = 2021), 0) AS extrabudgetary_resourses_documents_2021,
coalesce(sum(vfi.federal_budget_works / 1000) FILTER(WHERE vfi.year = 2021), 0) AS federal_budget_works_2021,
coalesce(sum(vfi.subject_budget_works / 1000) FILTER(WHERE vfi.year = 2021), 0) AS subject_budget_works_2021,
coalesce(sum(vfi.municipal_budget_works / 1000) FILTER(WHERE vfi.year = 2021), 0) AS municipal_budget_works_2021,
coalesce(sum(vfi.extrabudgetary_resourses_works / 1000) FILTER(WHERE vfi.year = 2021), 0) AS extrabudgetary_resourses_works_2021,
coalesce(sum(vfi.federal_budget_documents / 1000) FILTER(WHERE vfi.year = 2022), 0) AS federal_budget_documents_2022,
coalesce(sum(vfi.subject_documents / 1000) FILTER(WHERE vfi.year = 2022), 0) AS subject_budget_documents_2022,
coalesce(sum(vfi.municipal_budget_documents / 1000) FILTER(WHERE vfi.year = 2022), 0) AS municipal_budget_documents_2022,
coalesce(sum(vfi.extrabudgetary_resourses_documents / 1000) FILTER(WHERE vfi.year = 2022), 0) AS extrabudgetary_resourses_documents_2022,
coalesce(sum(vfi.federal_budget_works / 1000) FILTER(WHERE vfi.year = 2022), 0) AS federal_budget_works_2022,
coalesce(sum(vfi.subject_budget_works / 1000) FILTER(WHERE vfi.year = 2022), 0) AS subject_budget_works_2022,
coalesce(sum(vfi.municipal_budget_works / 1000) FILTER(WHERE vfi.year = 2022), 0) AS municipal_budget_works_2022,
coalesce(sum(vfi.extrabudgetary_resourses_works / 1000) FILTER(WHERE vfi.year = 2022), 0) AS extrabudgetary_resourses_works_2022,
coalesce(sum(vfi.federal_budget_documents / 1000) FILTER(WHERE vfi.year = 2023), 0) AS federal_budget_documents_2023,
coalesce(sum(vfi.subject_documents / 1000) FILTER(WHERE vfi.year = 2023), 0) AS subject_budget_documents_2023,
coalesce(sum(vfi.municipal_budget_documents / 1000) FILTER(WHERE vfi.year = 2023), 0) AS municipal_budget_documents_2023,
coalesce(sum(vfi.extrabudgetary_resourses_documents / 1000) FILTER(WHERE vfi.year = 2023), 0) AS extrabudgetary_resourses_documents_2023,
coalesce(sum(vfi.federal_budget_works / 1000) FILTER(WHERE vfi.year = 2023), 0) AS federal_budget_works_2023,
coalesce(sum(vfi.subject_budget_works / 1000) FILTER(WHERE vfi.year = 2023), 0) AS subject_budget_works_2023,
coalesce(sum(vfi.municipal_budget_works / 1000) FILTER(WHERE vfi.year = 2023), 0) AS municipal_budget_works_2023,
coalesce(sum(vfi.extrabudgetary_resourses_works / 1000) FILTER(WHERE vfi.year = 2023), 0) AS extrabudgetary_resourses_works_2023,
coalesce(sum(vfi.federal_budget_documents / 1000) FILTER(WHERE vfi.year = 2024), 0) AS federal_budget_documents_2024,
coalesce(sum(vfi.subject_documents / 1000) FILTER(WHERE vfi.year = 2024), 0) AS subject_budget_documents_2024,
coalesce(sum(vfi.municipal_budget_documents / 1000) FILTER(WHERE vfi.year = 2024), 0) AS municipal_budget_documents_2024,
coalesce(sum(vfi.extrabudgetary_resourses_documents / 1000) FILTER(WHERE vfi.year = 2024), 0) AS extrabudgetary_resourses_documents_2024,
coalesce(sum(vfi.federal_budget_works / 1000) FILTER(WHERE vfi.year = 2024), 0) AS federal_budget_works_2024,
coalesce(sum(vfi.subject_budget_works / 1000) FILTER(WHERE vfi.year = 2024), 0) AS subject_budget_works_2024,
coalesce(sum(vfi.municipal_budget_works / 1000) FILTER(WHERE vfi.year = 2024), 0) AS municipal_budget_works_2024,
coalesce(sum(vfi.extrabudgetary_resourses_works / 1000) FILTER(WHERE vfi.year = 2024), 0) AS extrabudgetary_resourses_works_2024
FROM volga_river_recovery.volga_financial_indicators vfi
WHERE vfi.volga_program_document_id IN (SELECT volga_program_document_id FROM cte_data1)
GROUP BY vfi.volga_program_document_id
)
SELECT *
FROM cte_data1 d1
LEFT JOIN cte_data2 d2 USING(volga_program_document_id)
LEFT JOIN cte_data3 d3 USING(volga_program_document_id)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment