--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)