Odchodovost zavrenych pobo

--VARIANTA 3, PODLE TOHO ZDA JE I PO 12 M PRIMÁRNÍM KLIENTEM
 SELECT * FROM (
WITH zavrene_pobocky AS
 (SELECT orgh.orgh_name,
         orgh.orgh_code,
         orgh.org_key,
         trunc(MIN(orgh.orgh_valid_from), 'MONTH') AS datum_uzavreni,
         add_months(trunc(MIN(orgh.orgh_valid_from), 'MONTH'), -3) AS baze_datum,
         add_months(trunc(MIN(orgh.orgh_valid_from), 'MONTH'), +12) AS future_datum
    FROM dwh_owner.organization_history orgh
   WHERE 1 = 1 /*do roku  2021*/
         AND orgh.orgh_level = 6
         AND orgh.orgh_deleted_flag = 'Y'
         AND orgh.orgh_source_system_id = 'HR0'
         AND orgh.orgh_reporting_region_name = 'POBOČKOVÁ SÍŤ'
         AND orgh.orgh_category_code IN ('BP', 'BZ', 'PP', 'PR', 'SP', 'UP', 'ZP')
         AND orgh.orgh_valid_to <> to_date('30000101', 'yyyymmdd')
         AND orgh.orgh_valid_from <= (trunc(SYSDATE) - 365)
   GROUP BY orgh.orgh_name,
            orgh.orgh_code,
            orgh.org_key),
klienti AS
 (SELECT zavrene_pobocky.orgh_name,
         zavrene_pobocky.orgh_code,
         zavrene_pobocky.org_key,
         zavrene_pobocky.datum_uzavreni,
         zavrene_pobocky.baze_datum,
         zavrene_pobocky.future_datum,
         phpred.pt_unified_key          AS pt_unified_key_pred,
         phpo.pt_unified_key            AS pt_unified_key_po,
         pcu.ptcls_key
    FROM zavrene_pobocky
    JOIN dwh_owner.party_history phpred
      ON zavrene_pobocky.org_key = phpred.org_key
         AND zavrene_pobocky.baze_datum BETWEEN phpred.pth_valid_from AND phpred.pth_valid_to
         AND phpred.ptori_key = 4 --zdroj CRM
         AND phpred.pttp_key = 1 --FO
         AND phpred.ptst_key = 6 --Klient
         AND phpred.pth_deleted_flag = 'N'
    JOIN ads_owner.party_properties pp
      ON phpred.pt_unified_key = pp.pt_key
         AND pp.ptori_key = 4
         AND pp.pptp_key = 1
         AND pp.ppr_param_flag = 'Y'
         AND pp.ppr_deleted_flag = 'N'
         AND zavrene_pobocky.baze_datum BETWEEN pp.ppr_valid_from AND pp.ppr_valid_to
    LEFT JOIN ads_owner.party_properties pp1
      ON phpred.pt_unified_key = pp1.pt_key
         AND pp1.ptori_key = 4
         AND pp1.pptp_key = 1
         AND pp1.ppr_param_flag = 'Y'
         AND pp1.ppr_deleted_flag = 'N'
         AND zavrene_pobocky.future_datum BETWEEN pp1.ppr_valid_from AND pp1.ppr_valid_to
    LEFT JOIN dwh_owner.party_history phpo
      ON phpred.pt_unified_key = phpo.pt_unified_key
         AND zavrene_pobocky.future_datum BETWEEN phpo.pth_valid_from AND phpo.pth_valid_to
         AND phpo.ptori_key = 4 --zdroj CRM
         AND phpo.pttp_key = 1 --FO
         AND phpo.ptst_key = 6 --Klient
         AND phpo.pth_deleted_flag = 'N'
    LEFT JOIN dwh_owner.party_classf_usages pcu
      ON pcu.pt_key = phpred.pt_unified_key
         AND pcu.ptori_key = 4
         AND pcu.ptclstp_key = 111 --reportingovy stav klienta
        --and ptcls_key in (29923,33538,33539) --umrti, zemrely s produktem, zemrely bez produktu
         AND zavrene_pobocky.future_datum BETWEEN pcu.ptclsus_valid_from AND pcu.ptclsus_valid_to),
vysledek AS
 (SELECT klienti.orgh_code,
         klienti.orgh_name,
         klienti.datum_uzavreni,
         klienti.pt_unified_key_pred AS klienti_3m_pred,
         CASE
           WHEN klienti.pt_unified_key_po IS NOT NULL AND klienti.ptcls_key NOT IN (29923, 33538, 33539) THEN
            1
           ELSE
            0
         END AS klienti_12m_po, --pokud mrtvola nepočítám do jako klienta
         CASE
           WHEN klienti.ptcls_key IN (29923, 33538, 33539) THEN
            '1'
           ELSE
            '0'
         END AS klient_zemrel
    FROM klienti)
SELECT 'PODLE_PRIMARNIHO_KLIENTA' AS varianta,
       vysledek.orgh_code,
       vysledek.orgh_name,
       vysledek.datum_uzavreni,
       COUNT(vysledek.klienti_3m_pred) AS klientu_3m_pred,
       SUM(vysledek.klienti_12m_po) AS klientu_12m_po,
       (COUNT(vysledek.klienti_3m_pred) - SUM(vysledek.klienti_12m_po)) - SUM(vysledek.klient_zemrel) AS klientu_odeslo,
       SUM(vysledek.klient_zemrel) AS pocet_zemrelych,
       ROUND(100*((COUNT(vysledek.klienti_3m_pred) - SUM(vysledek.klienti_12m_po) - SUM(vysledek.klient_zemrel)) / COUNT(vysledek.klienti_3m_pred)),2) AS odchodovost,
       ROUND(100*(SUM(vysledek.klient_zemrel) / COUNT(vysledek.klienti_3m_pred)),2) AS mortalita,
       ROUND(100*((COUNT(vysledek.klienti_3m_pred) - SUM(vysledek.klienti_12m_po)) / COUNT(vysledek.klienti_3m_pred)) ,2) AS odchodovost_vcetne_mortality
  FROM vysledek
 GROUP BY 'PODLE_PRIMARNIHO_KLIENTA',
          vysledek.orgh_code,
          vysledek.orgh_name,
          vysledek.datum_uzavreni 
 ORDER BY vysledek.datum_uzavreni DESC)