Temat: [Oracle] Sumowanie tylko unikalnych wartości
Witam,
Przepraszam ze nie wyraziłem się precyzyjnie chciałem uniknąć wklejanie tego co poniżej:)
To z czym sobie nie mogę poradzić jest częścią dłuższego zapytania, w którym wyciągam dane z 10 ostaniach miesięcy osobno (mam jeszcze analogiczne widoki dla dni i tygodni).
Gdyby nie ta liczba_polaczen, która jest dołączane z tabeli będącej zrzutem z innej bazy nie byłoby problemu - jedyne po czym mogę ją połączyć jest data, która powoduje multiplikacje (screen).
Dłuższe zapytanie korzysta z widoku przeliczeniowego:
SELECT
refno,
cc.acceptancedate poczatek,
cc.udf_koniec_obslugi koniec,
e.id,
e.eventdate,
kolumna,
CASE WHEN variant='H' THEN Amount END nal,
CASE WHEN variant='E' THEN Amount END plat,
cc.udf_status,
ILOSC_WYKONANYCH_POLACZEN
FROM ika_ccase cc
JOIN ika_event e ON e.ccaseid=cc.id
left JOIN klient_polaczenia ON DATA=e.eventdate
left join ika_raport_kody_sl ksl ON ksl.EVENTTEMPLATEID=e.EVENTTEMPLATEID and ksl.raport='klient'
tabelkę klient_polaczenia mógłbym dołączać dopiero w tym dłuższym zapytaniu, ale gdy kombinowałem z podselectem, był błąd z grupowaniem.
SELECT
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-10) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-9)-1 THEN refno END) sprawy_10,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-9) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-8)-1 THEN refno END) sprawy_09,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-8) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-7)-1 THEN refno END) sprawy_08,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-7) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-6)-1 THEN refno END) sprawy_07,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-6) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-5)-1 THEN refno END) sprawy_06,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-5) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-4)-1 THEN refno END) sprawy_05,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-4) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-3)-1 THEN refno END) sprawy_04,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-3) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-2)-1 THEN refno END) sprawy_03,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-2) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-1)-1 THEN refno END) sprawy_02,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-1) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-0)-1 THEN refno END) sprawy_01,
--daty
trim(To_Char(Add_Months(Trunc(SYSDATE,'mm'),-10),'yyyy-Month')) data_10,
trim(To_Char(Add_Months(Trunc(SYSDATE,'mm'),-9),'yyyy-Month')) data_09,
trim(To_Char(Add_Months(Trunc(SYSDATE,'mm'),-8),'yyyy-Month')) data_08,
trim(To_Char(Add_Months(Trunc(SYSDATE,'mm'),-7),'yyyy-Month')) data_07,
trim(To_Char(Add_Months(Trunc(SYSDATE,'mm'),-6),'yyyy-Month')) data_06,
trim(To_Char(Add_Months(Trunc(SYSDATE,'mm'),-5),'yyyy-Month')) data_05,
trim(To_Char(Add_Months(Trunc(SYSDATE,'mm'),-4),'yyyy-Month')) data_04,
trim(To_Char(Add_Months(Trunc(SYSDATE,'mm'),-3),'yyyy-Month')) data_03,
trim(To_Char(Add_Months(Trunc(SYSDATE,'mm'),-2),'yyyy-Month')) data_02,
trim(To_Char(Add_Months(Trunc(SYSDATE,'mm'),-1),'yyyy-Month')) data_01,
--należoności
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-10) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-9)-1 THEN nal END),0) nal_10,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-9) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-8)-1 THEN nal END),0) nal_09,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-8) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-7)-1 THEN nal END),0) nal_08,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-7) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-6)-1 THEN nal END),0) nal_07,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-6) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-5)-1 THEN nal END),0) nal_06,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-5) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-4)-1 THEN nal END),0) nal_05,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-4) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-3)-1 THEN nal END),0) nal_04,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-3) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-2)-1 THEN nal END),0) nal_03,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-2) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-1)-1 THEN nal END),0) nal_02,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-1) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-0)-1 THEN nal END),0) nal_01,
--płatności
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-10) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-9)-1 THEN plat END),0) plat_10,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-9) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-8)-1 THEN plat END),0) plat_09,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-8) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-7)-1 THEN plat END),0) plat_08,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-7) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-6)-1 THEN plat END),0) plat_07,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-6) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-5)-1 THEN plat END),0) plat_06,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-5) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-4)-1 THEN plat END),0) plat_05,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-4) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-3)-1 THEN plat END),0) plat_04,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-3) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-2)-1 THEN plat END),0) plat_03,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-2) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-1)-1 THEN plat END),0) plat_02,
nvl(Sum(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-1) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-0)-1 THEN plat END),0) plat_01,
-- ilość spraw z wpłatami
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-10) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND plat<>0 THEN refno END) spr_z_wplat_10,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-9) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND plat<>0 THEN refno END) spr_z_wplat_09,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-8) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND plat<>0 THEN refno END) spr_z_wplat_08,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-7) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND plat<>0 THEN refno END) spr_z_wplat_07,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-6) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND plat<>0 THEN refno END) spr_z_wplat_06,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-5) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND plat<>0 THEN refno END) spr_z_wplat_05,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-4) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND plat<>0 THEN refno END) spr_z_wplat_04,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-3) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND plat<>0 THEN refno END) spr_z_wplat_03,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-2) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND plat<>0 THEN refno END) spr_z_wplat_02,
count(distinct CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-1) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND plat<>0 THEN refno END) spr_z_wplat_01,
-- Ilość podjętych prób kontaktu
Nvl(count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-10) AND Add_Months(Trunc(SYSDATE,'mm'),-9)-1 THEN wykonywanych_polacz END),0) prob_kontaktu_10,
Nvl(count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-9) AND Add_Months(Trunc(SYSDATE,'mm'),-8)-1 THEN wykonywanych_polacz END),0) prob_kontaktu_09,
Nvl(count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-8) AND Add_Months(Trunc(SYSDATE,'mm'),-7)-1 THEN wykonywanych_polacz END),0) prob_kontaktu_08,
Nvl(count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-7) AND Add_Months(Trunc(SYSDATE,'mm'),-6)-1 THEN wykonywanych_polacz END),0) prob_kontaktu_07,
Nvl(count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-6) AND Add_Months(Trunc(SYSDATE,'mm'),-5)-1 THEN wykonywanych_polacz END),0) prob_kontaktu_06,
Nvl(count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-5) AND Add_Months(Trunc(SYSDATE,'mm'),-4)-1 THEN wykonywanych_polacz END),0) prob_kontaktu_05,
Nvl(count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-4) AND Add_Months(Trunc(SYSDATE,'mm'),-3)-1 THEN wykonywanych_polacz END),0) prob_kontaktu_04,
Nvl(count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-3) AND Add_Months(Trunc(SYSDATE,'mm'),-2)-1 THEN wykonywanych_polacz END),0) prob_kontaktu_03,
Nvl(count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-2) AND Add_Months(Trunc(SYSDATE,'mm'),-1)-1 THEN wykonywanych_polacz END),0) prob_kontaktu_02,
Nvl(count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-1) AND Add_Months(Trunc(SYSDATE,'mm'),-0)-1 THEN wykonywanych_polacz END),0) prob_kontaktu_01,
-- ilość kontaktów z dłużnikiem
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-10) AND Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND kolumna IN ('Kontakty In','Kontakty Out') THEN 1 END),0) kont_z_dluzn10,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-9) AND Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND kolumna IN ('Kontakty In','Kontakty Out') THEN 1 END),0) kont_z_dluzn09,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-8) AND Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND kolumna IN ('Kontakty In','Kontakty Out') THEN 1 END),0) kont_z_dluzn08,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-7) AND Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND kolumna IN ('Kontakty In','Kontakty Out') THEN 1 END),0) kont_z_dluzn07,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-6) AND Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND kolumna IN ('Kontakty In','Kontakty Out') THEN 1 END),0) kont_z_dluzn06,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-5) AND Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND kolumna IN ('Kontakty In','Kontakty Out') THEN 1 END),0) kont_z_dluzn05,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-4) AND Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND kolumna IN ('Kontakty In','Kontakty Out') THEN 1 END),0) kont_z_dluzn04,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-3) AND Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND kolumna IN ('Kontakty In','Kontakty Out') THEN 1 END),0) kont_z_dluzn03,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-2) AND Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND kolumna IN ('Kontakty In','Kontakty Out') THEN 1 END),0) kont_z_dluzn02,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-1) AND Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND kolumna IN ('Kontakty In','Kontakty Out') THEN 1 END),0) kont_z_dluzn01,
--kontaktów przychdozacych z dłużnikiem
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-10) AND Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND kolumna = 'Kontakty In' THEN 1 END),0) kont_przych_z_dluzn10,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-9) AND Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND kolumna = 'Kontakty In' THEN 1 END),0) kont_przych_z_dluzn09,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-8) AND Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND kolumna = 'Kontakty In' THEN 1 END),0) kont_przych_z_dluzn08,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-7) AND Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND kolumna = 'Kontakty In' THEN 1 END),0) kont_przych_z_dluzn07,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-6) AND Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND kolumna = 'Kontakty In' THEN 1 END),0) kont_przych_z_dluzn06,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-5) AND Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND kolumna = 'Kontakty In' THEN 1 END),0) kont_przych_z_dluzn05,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-4) AND Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND kolumna = 'Kontakty In' THEN 1 END),0) kont_przych_z_dluzn04,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-3) AND Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND kolumna = 'Kontakty In' THEN 1 END),0) kont_przych_z_dluzn03,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-2) AND Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND kolumna = 'Kontakty In' THEN 1 END),0) kont_przych_z_dluzn02,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-1) AND Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND kolumna = 'Kontakty In' THEN 1 END),0) kont_przych_z_dluzn01,
--kontaktów przychdozących (osoba niekompetentna)
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-10) AND Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND kolumna = 'Kontakty In niekompet' THEN 1 END),0) kont_przych_os_niekomp10,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-9) AND Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND kolumna = 'Kontakty In niekompet' THEN 1 END),0) kont_przych_os_niekomp09,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-8) AND Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND kolumna = 'Kontakty In niekompet' THEN 1 END),0) kont_przych_os_niekomp08,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-7) AND Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND kolumna = 'Kontakty In niekompet' THEN 1 END),0) kont_przych_os_niekomp07,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-6) AND Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND kolumna = 'Kontakty In niekompet' THEN 1 END),0) kont_przych_os_niekomp06,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-5) AND Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND kolumna = 'Kontakty In niekompet' THEN 1 END),0) kont_przych_os_niekomp05,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-4) AND Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND kolumna = 'Kontakty In niekompet' THEN 1 END),0) kont_przych_os_niekomp04,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-3) AND Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND kolumna = 'Kontakty In niekompet' THEN 1 END),0) kont_przych_os_niekomp03,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-2) AND Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND kolumna = 'Kontakty In niekompet' THEN 1 END),0) kont_przych_os_niekomp02,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-1) AND Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND kolumna = 'Kontakty In niekompet' THEN 1 END),0) kont_przych_os_niekomp01,
--ilość przyjętych deklaracji
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-10) AND Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND kolumna = 'Przyjete deklaracje' THEN 1 END),0) przyj_dekl_10,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-9) AND Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND kolumna = 'Przyjete deklaracje' THEN 1 END),0) przyj_dekl_09,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-8) AND Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND kolumna = 'Przyjete deklaracje' THEN 1 END),0) przyj_dekl_08,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-7) AND Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND kolumna = 'Przyjete deklaracje' THEN 1 END),0) przyj_dekl_07,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-6) AND Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND kolumna = 'Przyjete deklaracje' THEN 1 END),0) przyj_dekl_06,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-5) AND Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND kolumna = 'Przyjete deklaracje' THEN 1 END),0) przyj_dekl_05,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-4) AND Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND kolumna = 'Przyjete deklaracje' THEN 1 END),0) przyj_dekl_04,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-3) AND Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND kolumna = 'Przyjete deklaracje' THEN 1 END),0) przyj_dekl_03,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-2) AND Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND kolumna = 'Przyjete deklaracje' THEN 1 END),0) przyj_dekl_02,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-1) AND Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND kolumna = 'Przyjete deklaracje' THEN 1 END),0) przyj_dekl_01,
--ilośc odmów spłaty
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-10) AND Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND kolumna = 'Odmowy splaty' THEN 1 END),0) odm_splaty_10,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-9) AND Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND kolumna = 'Odmowy splaty' THEN 1 END),0) odm_splaty_09,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-8) AND Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND kolumna = 'Odmowy splaty' THEN 1 END),0) odm_splaty_08,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-7) AND Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND kolumna = 'Odmowy splaty' THEN 1 END),0) odm_splaty_07,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-6) AND Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND kolumna = 'Odmowy splaty' THEN 1 END),0) odm_splaty_06,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-5) AND Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND kolumna = 'Odmowy splaty' THEN 1 END),0) odm_splaty_05,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-4) AND Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND kolumna = 'Odmowy splaty' THEN 1 END),0) odm_splaty_04,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-3) AND Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND kolumna = 'Odmowy splaty' THEN 1 END),0) odm_splaty_03,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-2) AND Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND kolumna = 'Odmowy splaty' THEN 1 END),0) odm_splaty_02,
nvl(Sum(CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-1) AND Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND kolumna = 'Odmowy splaty' THEN 1 END),0) odm_splaty_01,
--ilośc spraw z kontaktem
count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-10) AND Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND kolumna in ('Kontakty In','Kontakty Out','Kontakty In niekompet','Kontakty Out niekompet') THEN refno END) spr_z_kontakt10,
count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-9) AND Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND kolumna in ('Kontakty In','Kontakty Out','Kontakty In niekompet','Kontakty Out niekompet') THEN refno END) spr_z_kontakt09,
count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-8) AND Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND kolumna in ('Kontakty In','Kontakty Out','Kontakty In niekompet','Kontakty Out niekompet') THEN refno END) spr_z_kontakt08,
count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-7) AND Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND kolumna in ('Kontakty In','Kontakty Out','Kontakty In niekompet','Kontakty Out niekompet') THEN refno END) spr_z_kontakt07,
count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-6) AND Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND kolumna in ('Kontakty In','Kontakty Out','Kontakty In niekompet','Kontakty Out niekompet') THEN refno END) spr_z_kontakt06,
count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-5) AND Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND kolumna in ('Kontakty In','Kontakty Out','Kontakty In niekompet','Kontakty Out niekompet') THEN refno END) spr_z_kontakt05,
count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-4) AND Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND kolumna in ('Kontakty In','Kontakty Out','Kontakty In niekompet','Kontakty Out niekompet') THEN refno END) spr_z_kontakt04,
count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-3) AND Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND kolumna in ('Kontakty In','Kontakty Out','Kontakty In niekompet','Kontakty Out niekompet') THEN refno END) spr_z_kontakt03,
count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-2) AND Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND kolumna in ('Kontakty In','Kontakty Out','Kontakty In niekompet','Kontakty Out niekompet') THEN refno END) spr_z_kontakt02,
count(distinct CASE WHEN EVENTDATE BETWEEN Add_Months(Trunc(SYSDATE,'mm'),-1) AND Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND kolumna in ('Kontakty In','Kontakty Out','Kontakty In niekompet','Kontakty Out niekompet') THEN refno END) spr_z_kontakt01,
--confirmed
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-10) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND kolumna in ('Confirmed') THEN 1 END) confirmed_10,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-9) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND kolumna in ('Confirmed') THEN 1 END) confirmed_09,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-8) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND kolumna in ('Confirmed') THEN 1 END) confirmed_08,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-7) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND kolumna in ('Confirmed') THEN 1 END) confirmed_07,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-6) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND kolumna in ('Confirmed') THEN 1 END) confirmed_06,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-5) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND kolumna in ('Confirmed') THEN 1 END) confirmed_05,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-4) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND kolumna in ('Confirmed') THEN 1 END) confirmed_04,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-3) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND kolumna in ('Confirmed') THEN 1 END) confirmed_03,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-2) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND kolumna in ('Confirmed') THEN 1 END) confirmed_02,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-1) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND kolumna in ('Confirmed') THEN 1 END) confirmed_01,
--repaid
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-10) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-9)-1 AND kolumna in ('Repaid') THEN 1 END) Repaid_10,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-9) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-8)-1 AND kolumna in ('Repaid') THEN 1 END) Repaid_09,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-8) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-7)-1 AND kolumna in ('Repaid') THEN 1 END) Repaid_08,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-7) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-6)-1 AND kolumna in ('Repaid') THEN 1 END) Repaid_07,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-6) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-5)-1 AND kolumna in ('Repaid') THEN 1 END) Repaid_06,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-5) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-4)-1 AND kolumna in ('Repaid') THEN 1 END) Repaid_05,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-4) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-3)-1 AND kolumna in ('Repaid') THEN 1 END) Repaid_04,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-3) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-2)-1 AND kolumna in ('Repaid') THEN 1 END) Repaid_03,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-2) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-1)-1 AND kolumna in ('Repaid') THEN 1 END) Repaid_02,
count(CASE WHEN koniec >= Add_Months(Trunc(SYSDATE,'mm'),-1) AND poczatek <= Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND EVENTDATE <= Add_Months(Trunc(SYSDATE,'mm'),-0)-1 AND kolumna in ('Repaid') THEN 1 END) Repaid_01
FROM rap_klient_monitoring_v
Ten post został edytowany przez Autora dnia 03.02.15 o godzinie 08:54