-- Post file -----------------------------------------------------------------------------------------------------------------------------------------------
select * from vista.abdpostfilestreet;
/*
TBDPOSTFILESTREETID  STREET                ZIP4  ZIPADDON  ZIP6    SHORTCITY        COUNTRY  ADDRSCANTON  STREETNR  STREETNRADDON  POLGNAME         POLGCANTON  BFSNR  LASTUPDATED          
-------------------  --------------------  ----  --------  ------  ---------------  -------  -----------  --------  -------------  ---------------  ----------  -----  -------------------  
391                  Allmendstrasse        8914  00        891400  Aeugst am Albis  CH       ZH           10        (null)         Aeugst am Albis  ZH          1      2017-09-14 16:00:41  
392                  Allmendstrasse        8914  00        891400  Aeugst am Albis  CH       ZH           12        (null)         Aeugst am Albis  ZH          1      2017-09-14 16:00:41  
393                  Allmendstrasse        8914  00        891400  Aeugst am Albis  CH       ZH           13        (null)         Aeugst am Albis  ZH          1      2017-09-14 16:00:41  
*/
select * from vista.abdpostfilezip;
/*
TBDPOSTFILEZIPID  ZIP4  ZIPADDON  ZIP6    CITY                         SHORTCITY           COUNTRY  BFSNR  POLGNAME              CANTON  LASTUPDATED          
----------------  ----  --------  ------  ---------------------------  ------------------  -------  -----  --------------------  ------  -------------------  
1                 8914  00        891400  Aeugst am Albis              Aeugst am Albis     CH       1      Aeugst am Albis       ZH      2017-09-14 16:00:14  
2                 8914  02        891402  Aeugstertal                  Aeugstertal         CH       1      Aeugst am Albis       ZH      2017-09-14 16:00:14  
3                 8909  00        890900  Zwillikon                    Zwillikon           CH       2      Affoltern am Albis    ZH      2017-09-14 16:00:14  
*/
select count(*) from vista.abdpostfilezip; -- 5293
select count(*) from vista.abdpostfilestreet; -- 2094181

select * from vista.abdpostfilestreet where street='Postfach';

-- POLG Fusion / Gemeindefusion:
-- 163209 - ZH - Gemeindefusionen (Hofstetten-Elgg)
WITH x AS (
    select tpvpartid, kpvpartid, kpvpartid_ahvbranchnr, name, additionalname, clegaltype, munauthornr, ahvbranchnr, cmunauthorflg, bfsmunitipalnr, validfrom, validto, lastupdated 
    from vista.tpvpartner where (bfsmunitipalnr in (222, 271, 294) or ahvbranchnr in (93 , 88)) and clegaltype='NNPersonTy#POLG'
)
select tpvpartid, kpvpartid, kpvpartid_ahvbranchnr, name, additionalname, clegaltype, munauthornr, ahvbranchnr, cmunauthorflg, bfsmunitipalnr, validfrom, validto, lastupdated 
from vista.tpvpartner where kpvpartid in ( select x.kpvpartid from x) order by kpvpartid, bfsmunitipalnr;
/*
TPVPARTID   KPVPARTID  KPVPARTID_AHVBRANCHNR  NAME                 ADDITIONALNAME  CLEGALTYPE       MUNAUTHORNR  AHVBRANCHNR  CMUNAUTHORFLG  BFSMUNITIPALNR  VALIDFROM   VALIDTO     LASTUPDATED          
----------  ---------  ---------------------  -------------------  --------------  ---------------  -----------  -----------  -------------  --------------  ----------  ----------  -------------------  
963520      963521     25553713               Gemeinde Elgg        (null)          NNPersonTy#POLG  88           (null)       FLGAllocat#T   217             1900-01-01  2009-12-31  2017-09-13 08:19:11  
215641405   963521     25553713               Gemeinde Elgg        (null)          NNPersonTy#POLG  88           (null)       FLGAllocat#T   217             2010-01-01  2017-12-31  2017-12-12 09:32:59  
3092742143  963521     25553713               Gemeinde Elgg        (null)          NNPersonTy#POLG  88           (null)       FLGAllocat#T   294             2018-01-01  9999-12-31  2017-12-12 09:32:59  
215648486   963789     25166686               Gemeinde Hofstetten  (null)          NNPersonTy#POLG  93           (null)       FLGAllocat#T   222             2010-01-01  9999-12-31  2011-08-12 12:29:22  
963784      963789     25166686               Gemeinde Hofstetten  (null)          NNPersonTy#POLG  93           (null)       FLGAllocat#T   222             1900-01-01  2009-12-31  2017-09-13 08:42:37  
*/

WITH x AS (
    select tpvpartid, kpvpartid, kpvpartid_ahvbranchnr, name, additionalname, clegaltype, munauthornr, ahvbranchnr, cmunauthorflg, bfsmunitipalnr, validfrom, validto, lastupdated 
    from vista.tpvpartner where ahvbranchnr in (93, 88) and clegaltype='NNPersonTy#AHVZ'
)
select tpvpartid, kpvpartid, kpvpartid_ahvbranchnr, name, additionalname, clegaltype, munauthornr, ahvbranchnr, cmunauthorflg, bfsmunitipalnr, validfrom, validto, lastupdated 
from vista.tpvpartner where kpvpartid in ( select x.kpvpartid from x ) order by kpvpartid, ahvbranchnr;
/*
TPVPARTID  KPVPARTID  KPVPARTID_AHVBRANCHNR  NAME             ADDITIONALNAME  CLEGALTYPE       MUNAUTHORNR  AHVBRANCHNR  CMUNAUTHORFLG  BFSMUNITIPALNR  VALIDFROM   VALIDTO     LASTUPDATED          
---------  ---------  ---------------------  ---------------  --------------  ---------------  -----------  -----------  -------------  --------------  ----------  ----------  -------------------  
25166682   25166686   (null)                 AHV-Zweigstelle  Hofstetten      NNPersonTy#AHVZ  (null)       93           (null)         (null)          1900-01-01  9999-12-31  2011-08-10 08:45:52  
25553711   25553713   (null)                 AHV-Zweigstelle  Elgg            NNPersonTy#AHVZ  (null)       88           (null)         (null)          1900-01-01  9999-12-31  2011-08-10 09:07:29  
*/
select p.kpvpartid, p.tpvpartid, p.name, p.firstname, p.birthdate, p.socialsecnr, p.personid, p.kpvpartid_ahvbranchnr, 
(select max(p2.ahvbranchnr) from vista.tpvpartner p2 where p2.kpvpartid = p.kpvpartid_ahvbranchnr) as ahv_no_partner,
p.validfrom, p.validto, a.kpvadrsid, a.tpvadrsid, a.caddresstype, a.kpvpartid_munauthor,
(select max(p3.munauthornr) from vista.tpvpartner p3 where p3.kpvpartid = a.kpvpartid_munauthor) as ahv_no_address,
a.validfrom, a.validto, t.kpvtaxdid, t.tpvtaxdid, t.kpvpartid_taxdomicile, 
(select max(p4.munauthornr) from vista.tpvpartner p4 where p4.kpvpartid = t.kpvpartid_taxdomicile) as ahv_no_taxdata,
t.validfrom, t.validto
from vista.tpvpartner p
left join vista.tpvaddress a on (a.kpvpartid = p.kpvpartid and a.validto = '9999-12-31' and a.caddresstype='AddressTyp#WS')
left join vista.tpvtaxdata t on (t.kpvpartid = p.kpvpartid and t.validto = '9999-12-31')
where (
    p.kpvpartid_ahvbranchnr in (25166686) or 
    a.kpvpartid_munauthor in (963789) or 
    t.kpvpartid_taxdomicile in (963789) 
)
and p.cpartnertype='PartnerTyp#NP' and p.validto = '9999-12-31'
order by p.kpvpartid;
/*
KPVPARTID   TPVPARTID   NAME            FIRSTNAME    BIRTHDATE   SOCIALSECNR  PERSONID       KPVPARTID_AHVBRANCHNR  AHV_NO_PARTNER  VALIDFROM   VALIDTO     KPVADRSID   TPVADRSID   CADDRESSTYPE   KPVPARTID_MUNAUTHOR  AHV_NO_ADDRESS  VALIDFROM   VALIDTO     KPVTAXDID   TPVTAXDID   KPVPARTID_TAXDOMICILE  AHV_NO_TAXDATA  VALIDFROM   VALIDTO     
----------  ----------  --------------  -----------  ----------  -----------  -------------  ---------------------  --------------  ----------  ----------  ----------  ----------  -------------  -------------------  --------------  ----------  ----------  ----------  ----------  ---------------------  --------------  ----------  ----------  
3275297     3446404723  Schaufelberger  Hildegard    1960-07-02  (null)       7568666373119  25166686               93              1983-08-05  9999-12-31  36221229    5355201400  AddressTyp#WS  963521               88              2020-05-30  9999-12-31  84629045    3331786663  963521                 88              2019-01-01  9999-12-31  
5099070     178316551   Keller          Stephanie    1977-10-14  53677814159  7563085107291  25166686               93              2011-04-26  9999-12-31  27771758    3263723513  AddressTyp#WS  963521               88              2018-03-01  9999-12-31  178316557   3162018472  963521                 88              2018-01-01  9999-12-31  
5393159     6856723162  Koch            Annika       1977-01-20  55177520127  7564815975210  25166686               93              2006-05-02  9999-12-31  37109840    2835049572  AddressTyp#WS  964064               142             2017-06-01  9999-12-31  70037382    133745539   963653                 101             2010-01-01  9999-12-31  
6296027     6296085     Renner          Fabienne     1988-01-24  73688524110  7569598079353  25166686               93              1988-01-24  9999-12-31  35286606    5356090040  AddressTyp#WS  963521               88              2020-05-30  9999-12-31  69515286    3162018612  963521                 88              2018-01-01  9999-12-31  
6977833     3615963294  Küpfer          Jonas        1991-05-28  (null)       7569362566218  25166686               93              2016-04-22  9999-12-31  36249703    3169540496  AddressTyp#WS  963521               88              2018-01-01  9999-12-31  133634398   133634396   963521                 88              2010-01-01  9999-12-31  
8201952     3615965985  Küpfer          Linda        1991-05-19  (null)       7560257427843  25166686               93              2016-04-22  9999-12-31  28468927    3615965990  AddressTyp#WS  963521               88              2018-01-01  9999-12-31  75341362    4354798884  963521                 88              2020-01-01  9999-12-31  
(...)
*/

-- Old countries in UPI / PV
select c.code_id, c.shortcode, c.value, d.long_descript, pv.code_id as country_pv from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=4)
left join vista.tiscmcode pv on (pv.value = c.value and pv.code_id like 'Country#%')
where c.code_id like ('BSV%') and pv.code_id is null
order by c.value;
/*
CODE_ID      SHORTCODE   VALUE  LONG_DESCRIPT           COUNTRY_PV  
-----------  ----------  -----  ----------------------  ----------  
BSVLAND#208  208         208    GDR                     (null)      
BSVLAND#213  213         213    Gibraltar               (null)      
BSVLAND#220  220         220    Yugoslavia              (null)      
BSVLAND#235  235         235    USSR                    (null)      
BSVLAND#238  238         238    Czechoslovakia          (null)      
BSVLAND#249  249         249    Serbien und Montenegro  (null)      
BSVLAND#363  363         363    South Sudan             (null)      
BSVLAND#531  531         531    Vietnam                 (null)      
BSVLAND#538  538         538    Yemen                   (null)      
BSVLAND#540  540         540    Vietnam                 (null)      
BSVLAND#547  547         547    Timor-Leste             (null)      
BSVLAND#732  732         732    Western Sahara          (null)      
*/

-- Partners having "old" countries in IK:
select distinct p.ccountryofliving as pv_countryofliving, p.ccitizenship as pv_citizenship,
(select d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id = p.ccitizenship) as pv_citizenship_name,
h.ccountry as ik_country,
(select d.long_descript from vista.tiscmcode c 
left join vista.tiscmcode_descr d on (d.codetypeno = c.codetypeno and d.codeno = c.codeno and d.language=1)
where c.code_id = h.ccountry) as bsv_country_name
from vista.tpvpartner p
left join vista.tpvrole r on (r.kpvpartid = p.kpvpartid)
left join vista.tikowner o on (o.kpvroleid = r.kpvroleid)
left join vista.tikheader h on (h.tikownrid = o.tikownrid)
where h.ccountry in ('BSVLAND#208', 'BSVLAND#220', 'BSVLAND#235', 'BSVLAND#238', 'BSVLAND#531', 'BSVLAND#538');

-- 232432 - TG - Pro Rolle und Korrespondenzempfänger-Art [KorrespArt] nicht mehr als einen Eintrag geben
select rr1.kpvroleid_source, rr1.kpvroleid_target, count(distinct rr1.kpvrrelid) as cnt_reks from vista.tpvrolerel rr1
left join vista.tpvrolerel rr2 on (
    rr2.kpvroleid_source = rr1.kpvroleid_source and rr1.kpvroleid_target = rr2.kpvroleid_target 
    and rr2.kpvrrelid <> rr1.kpvrrelid and rr1.ccorraddresseetype = rr2.ccorraddresseetype
    and rr1.validto >= current date
)
where rr1.ccorraddresseetype = 'Correspond#REK' 
and rr1.validto >= current date
group by rr1.kpvroleid_source, rr1.kpvroleid_target
having count(distinct rr1.kpvrrelid) > 1
order by 3 desc;
/*
KPVROLEID_SOURCE  KPVROLEID_TARGET  CNT_REKS  
----------------  ----------------  --------  
680070107         9987995           2         
930530290         25191797          2         
4706068436        1627668           2         
*/

-- UPI Reconciliation batch workload (estimated, not exact!) - PVJURCON
with x as (
    select distinct upi.turupiregisterid, upi.old_ahv_or_nnss, upi.new_nnss, upi.statuscd, upi.actualitycd, upi.delivery_date, upi.lastupdated as upi_lastupdated,
    upi.deathdate as upi_death,
    p.kpvpartid, p.personid, p.lastupdated as p_lastupdated, p.DEATHDATE, p.VALIDTO,
    (select count(distinct r.kpvroleid) from vista.tpvrole r where r.kpvpartid = p.kpvpartid and r.croletype = 'Rolle#IK') as cnt_ik,
    (select count(distinct r2.kpvroleid) from vista.tpvrole r2 where r2.kpvpartid = p.kpvpartid and r2.croletype <> 'Rolle#IK' and r2.croletype <> 'Rolle#STA') as cnt_others,
    (select count(distinct a.kpvadrsid) from vista.tpvaddress a where a.kpvpartid = p.kpvpartid) as cnt_a
    from vista.aurupiregister upi
    join vista.tpvpartner p on ( (p.personid = upi.old_ahv_or_nnss or p.personid = upi.new_nnss) and p.validto > current date)
    where delivery_date between '2021-01-01' and '2021-01-04' -- date range
    and
    ( 
        statuscd = '2' and actualitycd='0' -- set to inactive
        or
        statuscd = '3' and actualitycd='0' -- set to invalid 
        or
        statuscd in ( '0' ,'1' ) and actualitycd = '1' -- current entries
    )
)
select * from x where not (x.cnt_ik = 1 and x.cnt_others = 0 and x.cnt_a = 0) -- 5036
and (x.upi_death is not null and x.deathdate is null) -- 132
;

-- KEP Error list:
select date(proc.messagedate) as "Meldedatum", p1.vn as "NNSS", 
p1.officialname as "Nachname", p1.firstname as "Vorname", p1.dateofbirth as "Geb.-dat.", p1.deathdate as "Todesdatum", --p1.status as "Status",
cast (
    case 
    when p1.maritalstatus = 1 then '1: #L'
    when p1.maritalstatus = 2 then '2: #H/G'
    when p1.maritalstatus = 3 then '3: #W'
    when p1.maritalstatus = 4 then '4: #S'
    when p1.maritalstatus = 5 then '5: #L'
    when p1.maritalstatus = 6 then '6: #GPS/GRP'
    when p1.maritalstatus = 7 then '7: #GGP/GTP'
    when p1.maritalstatus = 9 then '9: #U/L'
    end
as varchar(21))
as "Zivilstand", p1.dateofmaritalstatus as "Dat. Ziv.st.-Aenderung", 
a.arrivaldate as "Zuzugsdatum", a.departuredate as "Wegzugsdatum", a.movingdate as "Umzugsdatum",
a.fromcanton as "Zuzugskanton", a.frommunicipalityname as "Zuzugsgemeinde", a.tocanton as "Wegzugskanton", a.tomunicipalityname as "Wegzugsgemeinde",
a.residencetype as "Meldeverh.", (a.street || ' ' || a.housenumber || ', ' || a.swisszipcode || ' ' || a.town) as "KEP-Adresse",
(vab.street || ' ' || va.streetnr || ', ' || left(coalesce(vab.zip, '    '), 4) || ' ' || vab.city) as "PV-Wohnsitz",
(vzab.street || ' ' || vza.streetnr || ', ' || left(coalesce(vzab.zip, '    '), 4) || ' ' || vzab.city) as "PV-Aufenthalt"
from vista.tpvkeppartner p1
join vista.tpvkepprocessing proc on (proc.tpvkepprocid = p1.tpvkepprocid)
left join vista.tpvkepaddress a on (a.tpvkeppartid = p1.tpvkeppartid)
left join vista.tpvpartner vp on (vp.personid = p1.vn and vp.validto > current date)
left join vista.tpvaddress va on (va.kpvpartid = vp.kpvpartid and va.caddresstype = 'AddressTyp#WS' and va.validto > current date)
left join vista.tpvaddressbasic vab on (vab.kpvadbaid = va.kpvadbaid and vab.validto > current date)
left join vista.tpvaddress vza on (vza.kpvpartid = vp.kpvpartid and vza.caddresstype = 'AddressTyp#AA' and vza.validto > current date)
left join vista.tpvaddressbasic vzab on (vzab.kpvadbaid = vza.kpvadbaid and vzab.validto > current date)
where p1.status = 'E' and p1.ismainpartner = 1
and proc.messagedate between '2022-03-30' and '2022-06-30'
order by proc.tpvkepprocid asc, proc.messagedate asc, p1.vn asc;

-- QR-IBANs
select * from vista.tpvpaymentaddr where iban like 'CH%' and substr(iban, 3, 1) = '3';
select count(kpvpaddid) from vista.tpvpaymentaddr where iban like 'CH%' and substr(iban, 3, 1) = '3'; -- 123'690 ZH
select * from vista.tpvpaymentaddr where iban like 'CH%' and substr(iban, 5, 1) = '3';
select count(kpvpaddid) from vista.tpvpaymentaddr where iban like 'CH%' and substr(iban, 5, 1) = '3'; -- 27 ZH
select p.* from vista.tpvpaymentaddr pa
left join vista.tpvpartner p on p.kpvpartid = pa.kpvpartid
where pa.iban like 'CH%' and substr(iban, 5, 1) = '3';
select kpvbankid, clearingnr, bankname from vista.tpvbankbasic where clearingnr like '3%' and ccountry='Country#CH' and validto > current date;
/*
KPVBANKID   CLEARINGNR  BANKNAME                    
----------  ----------  --------------------------  
5483128845  30700       Zürcher Kantonalbank        
5569648034  31000       Credit Suisse (Schweiz) AG  
5581994239  30808       Raiffeisen                  
6110827949  30220       VP Bank (Schweiz) AG        
6273895157  30024       Valiant Bank AG             
6407318185  30000       Postfinance AG              
*/

-- Duplicate IBANs (on same partner):
select iban1.kpvpartid, iban1.iban as iban1_iban, iban1.kpvpaddid as iban1_kpvpaddid, iban2.iban as iban2_iban, iban2.kpvpaddid as iban2_kpvpaddid
from vista.tpvpaymentaddr iban1
left join vista.tpvpaymentaddr iban2 on (iban2.kpvpartid = iban1.kpvpartid and iban2.kpvpaddid <> iban1.kpvpaddid and iban2.iban = iban1.iban)
where iban1.iban is not null and iban2.iban is not null;
/*
KPVPARTID  IBAN1_IBAN             IBAN1_KPVPADDID  IBAN2_IBAN             IBAN2_KPVPADDID  
---------  ---------------------  ---------------  ---------------------  ---------------  
966285     CH0708401016724768903  226804063        CH0708401016724768903  38609282         
966285     CH0708401016724768903  38609282         CH0708401016724768903  226804063        
969046     CH3800700115200625730  38837637         CH3800700115200625730  245423205        
969046     CH3800700115200625730  38837637         CH3800700115200625730  245423205        
969046     CH3800700115200625730  245423205        CH3800700115200625730  38837637         
969046     CH3800700115200625730  245423205        CH3800700115200625730  38837637         
973082     CH9204835025521060000  39177418         CH9204835025521060000  39292127         
973082     CH9204835025521060000  39292127         CH9204835025521060000  39177418         
(...)
*/

-- Duplicate WS address:
select ws1.kpvpartid, ws1.caddresstype as ws1_type, ws1.kpvadrsid as ws1_kpvadrsid, ws2.caddresstype as ws2_type, ws2.kpvadrsid as ws2_kpvadrsid
from vista.tpvaddress ws1
left join vista.tpvaddress ws2 on (ws2.kpvpartid = ws1.kpvpartid and ws2.kpvadrsid <> ws1.kpvadrsid and ws2.caddresstype = ws1.caddresstype)
where ws1.caddresstype='AddressTyp#WS' and ws2.caddresstype='AddressTyp#WS' and ws1.validto > current date and ws2.validto > current date;

-- Legal representation / guardian: source / target roles
select rr.tpvrrelid, rr.kpvrrelid, rr.crelationtype, rr.clegalguardtype, rr.kpvroleid_source, rs.croletype, rr.kpvroleid_target, rt.croletype from vista.tpvrolerel rr 
left join vista.tpvrole rs on (rs.kpvroleid = rr.kpvroleid_source)
left join vista.tpvrole rt on (rt.kpvroleid = rr.kpvroleid_target)
where rr.crelationtype='RoleRelati#RV' order by rr.lastupdated desc;
/*
TPVRRELID   KPVRRELID   CRELATIONTYPE  CLEGALGUARDTYPE  KPVROLEID_SOURCE  CROLETYPE  KPVROLEID_TARGET  CROLETYPE  
----------  ----------  -------------  ---------------  ----------------  ---------  ----------------  ---------  
1020855865  1020855866  RoleRelati#RV  (null)           181789532         Rolle#VB   44051460          Rolle#VET  
44039717    44039718    RoleRelati#RV  (null)           42891728          Rolle#MA   44039687          Rolle#VET  
996930882   996930883   RoleRelati#RV  (null)           176112924         Rolle#VB   44038785          Rolle#VET  
*/
select rr.tpvrrelid, rr.kpvrrelid, rr.crelationtype, rr.clegalguardtype, rr.kpvroleid_source, rs.croletype, rr.kpvroleid_target, rt.croletype from vista.tpvrolerel rr 
left join vista.tpvrole rs on (rs.kpvroleid = rr.kpvroleid_source)
left join vista.tpvrole rt on (rt.kpvroleid = rr.kpvroleid_target)
where rr.crelationtype='RoleRelati#GV' order by rr.lastupdated desc;
/*
TPVRRELID   KPVRRELID   CRELATIONTYPE  CLEGALGUARDTYPE  KPVROLEID_SOURCE  CROLETYPE  KPVROLEID_TARGET  CROLETYPE  
----------  ----------  -------------  ---------------  ----------------  ---------  ----------------  ---------  
8908525344  1687407140  RoleRelati#GV  LegalAgent#BMV   10561167          Rolle#STA  1200277517        Rolle#VET  
1687407139  1687407140  RoleRelati#GV  LegalAgent#BMV   10561167          Rolle#STA  1200277517        Rolle#VET  
8901859794  8901859795  RoleRelati#GV  LegalAgent#BMV   8804142           Rolle#STA  3194013552        Rolle#VET  
*/

-- KEP / SEDEX:
select * from vista.TZAXMLSDXRECORD
where date(lastupdated) >= current date - 5 days
and MESSAGETYPE in ('1118', '10099'); -- note: only 1118 used for KEP

-- KEP: Postkörbe - Analyse ----------------------
-- 28.03.2022

-- Wie viele PBs pro Typ? (NULL = kein PB ausgelöst)
select pbtype, count(*) as cnt_pb from vista.tpvkeppb group by pbtype order by 2 desc;
/*
PBTYPE  CNT_PB  
------  ------  
(null)  28879   
KPAD    23174   
KPSP    9636    
KPFR    6225    
KPGT    3911    
KPTD    164     
*/
-- Gibt es "Ausreisser" (Massenhaft PKs pro Tag)?
select date(lastupdated) as day_kpad, count(*) as cnt_kpad from vista.tpvkeppb where pbtype='KPAD' group by date(lastupdated) order by 2 desc;
/*
DAY_KPAD    CNT_KPAD  
----------  --------  
2022-01-05  1235      
2022-03-01  916       
2022-01-11  780       
2022-02-01  661       
2022-03-14  652       
2022-01-26  642       
2022-01-28  635       
2022-02-24  598       
2022-02-07  594       
2022-03-22  572       
2022-02-28  571       
(...)
*/
-- Wie viele PBs total?
-- Hinweis: Nur Daten für 3 Monate zurück (Clean-Up-Job löscht alte Einträge!)
select count(*) from vista.tpvkeppb pb where pb.pbtype is not null and date(pb.lastupdated) between '2022-01-01' and '2022-03-27'; -- Total PBs: 42'107 (in ca. 3 Monaten)
-- Davon KPADs?
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27'; -- 22'842 (54%)
-- Davon pro Monat?
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-01-31'; -- 8'022
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-02-01' and '2022-02-28'; -- 8'015
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-03-01' and '2022-03-28'; -- 6'805 (allerdings Monat noch nicht ganz fertig... )

-- Fallen EL-Fälle ins Gewicht? (dürften nicht automatisch mutiert werden) --> nein:
select count(pb.tpvkeppbid) from vista.tpvkeppb pb join vista.tpvrole r on (r.kpvpartid = pb.kpvpartid and r.croletype = 'Rolle#EL') 
where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-28'; -- 210 --> nur ca. 1% aller KPADs betreffen einen EL-Fall --> Fällt kaum ins Gewicht...

-- Zuzugsdatum:
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and karrivaldate is not null; -- 22'842 --> ArrivalDate wird immer gesetzt!
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and karrivaldate is null; -- 0 (siehe oben)
-- Umzugsdatum:
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and kmovingdate is not null; -- 10'240 --> ca. 50% aller KPADs
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and kmovingdate > karrivaldate; -- 10'240 --> also immer!
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and kmovingdate < karrivaldate; -- 0 (siehe oben)
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and kmovingdate > karrivaldate; -- 10'240 --> also immer!
-- Berücksichtigung "16 Monate":
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and kmovingdate > (date('2022-03-28') - 480 days) ; -- 8'438 (Umzugsdatum jünger als 16 Monate)
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and kmovingdate < (date('2022-03-28') - 480 days) ; -- 1'802 (Umzugsdatum älter als 16 Monate)
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and kmovingdate is null and karrivaldate < (date('2022-03-28') - 480 days) ; -- 3'861 (Ankunftsdatum jünger als 16 Mte.)
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and kmovingdate is null and karrivaldate > (date('2022-03-28') - 480 days) ; -- 8'740 (Ankunftsdatum älter als 16 Mte.)
-- veraltete Meldungen ganz ignorieren (weder Mutation noch PK):
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and 
( (kmovingdate is null and karrivaldate < (date('2022-03-28') - 480 days)) or kmovingdate < (date('2022-03-28') - 480 days)) ; -- 5'663 "veraltete" Meldungen (25% aller KPADs, bzw. 13 % aller PKs)
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and 
( (kmovingdate is null and karrivaldate > (date('2022-03-28') - 480 days)) or kmovingdate > (date('2022-03-28') - 480 days)) ; -- 17'178 "aktuelle" Meldungen (75% aller KPADs, bzw. 37 % aller PKs)
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and 
( (kmovingdate is null and karrivaldate > (date('2022-03-28') - 480 days)) or kmovingdate > (date('2022-03-28') - 480 days)) and
kaddressline1 is null and kaddressline2 is null; -- 15'772 "aktuelle" Meldungen OHNE Adresszusatz (69% aller KPADs)
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and 
( (kmovingdate is null and karrivaldate > (date('2022-03-28') - 480 days)) or kmovingdate > (date('2022-03-28') - 480 days)) and
kaddressline1 is null and kaddressline2 is null
and dstreet = 0; -- 2'193 "aktuelle" Meldungen OHNE Adresszusatz und gleicher Strasse
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and 
( (kmovingdate is null and karrivaldate > (date('2022-03-28') - 480 days)) or kmovingdate > (date('2022-03-28') - 480 days)) and
kaddressline1 is null and kaddressline2 is null
and dstreet = 0 and (dewid = 1 or degid = 1); -- 2'161 "aktuelle" Meldungen OHNE Adresszusatz und gleicher Strasse, nur EWID / EGID unterschiedlich --> Automat. Setzen von EWID / EGID
select count(*) from vista.tpvkeppb pb where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and 
( (kmovingdate is null and karrivaldate > (date('2022-03-28') - 480 days)) or kmovingdate > (date('2022-03-28') - 480 days)) and
kaddressline1 is null and kaddressline2 is null
and vstreet is null; -- 2'090 "aktuelle" Meldungen OHNE Adresszusatz, keine Adresse in VISTA --> Kandidaten für automat. neue Adresse erstellen.

-- Abschätzung KEP-Meldung neuer als VISTA:
-- Zunächst: unterschiedliche Strasse gemeldet, aktuelle Periode hat (mittlerweile) das gleiche Datum wie Zuzug/Umzug (war zum Zeitpunkt des PKs ev. nicht so!)
select count(*) from vista.tpvkeppb pb 
left join vista.tpvaddress a on (a.kpvpartid = pb.kpvpartid and a.caddresstype='AddressTyp#WS' and a.validto='9999-12-31')
where pb.pbtype='KPAD' and date(pb.lastupdated) between '2022-01-01' and '2022-03-27' and 
( (kmovingdate is null and karrivaldate > (date('2022-03-28') - 480 days)) or kmovingdate > (date('2022-03-28') - 480 days)) and
kaddressline1 is null and kaddressline2 is null
and dstreet = 1 and 
(a.validfrom = kmovingdate or a.validfrom = karrivaldate); -- 7'295
-- (...)

-- LZ.PV-N001 - Contact Center Notification:
select * from vista.tsfmqoutbound o where o.jmstype='LZ.PV-N001' order by o.lastupdated desc;
select o.reason, count(*) as cnt_reason from vista.tsfmqoutbound o where o.jmstype='LZ.PV-N001' group by o.reason order by 2 desc;
/*
REASON                CNT_REASON  
--------------------  ----------  
(null)                713629      
MQOUTINFO#IGNOREREPL  5923        
MQOUTBREJ#BEANERROR   521         
MQOUTBREJ#INVOUTXML   225         
MQOUTBREJ#UNTECHERR   47          
*/

-- 285399 - ZH - KEP work unit Errors
select s.tzaxmlsdxrecordid, s.messagetype, s.state, s.lastupdated from vista.tzaxmlsdxrecord s where s.messagetype=1118 and state=2 
order by lastupdated desc fetch first 20 rows only;
/*
TZAXMLSDXRECORDID  MESSAGETYPE  STATE  LASTUPDATED          
-----------------  -----------  -----  -------------------  
8586293121         1118         2      2022-05-09 21:35:33  
7803601939         1118         2      2022-01-04 21:40:03  
7680189465         1118         2      2021-12-14 21:49:07  
7589221435         1118         2      2021-11-30 21:35:39  
7564224906         1118         2      2021-11-29 21:34:18  
7420603665         1118         2      2021-11-09 21:34:14  
7315917570         1118         2      2021-10-29 21:34:05  
7269934204         1118         2      2021-10-22 21:35:43  
7233527468         1118         2      2021-10-15 21:40:20  
7164590532         1118         2      2021-10-04 21:34:16  
6884349438         1118         2      2021-08-18 21:33:01  
6785533941         1118         2      2021-07-30 21:32:57  
*/