CREATE table t_chcode_ambitocarta_1116_1017 AS
SELECT chcode, ambito_carta FROM iccrea_tm.anagrafica_1116_1017 where chcode in (SELECT distinct chcode from iccrea_tm.tabella_recall_1116_1017);

--droppare le vecchie

CREATE VIEW v_carta_piu_usata_1116_1017 AS
SELECT DISTINCT vv.chcode,
                vv.pan_cifrato_sadas,
                vv.carta_piu_usata
FROM
  (SELECT ff.chcode,
          ff.pan_cifrato_sadas,
          max(cards) OVER (PARTITION BY ff.chcode) carta_piu_usata
   FROM
     (SELECT DISTINCT *
      FROM
        (SELECT v1.chcode,
                v1.pan_cifrato_sadas,
                count(v1.pan_cifrato_sadas) OVER (PARTITION BY v1.chcode, v1.pan_cifrato_sadas) cards
         FROM
           (SELECT chcode,
                   pan_cifrato_sadas,
                   cap_residenza
            FROM iccrea_tm.anagrafica_titolari_carte) v1
         INNER JOIN iccrea_tm.transazioni_1116_1017_in_anag_12mm v2 ON v1.chcode = v2.chcode
         AND v1.pan_cifrato_sadas = v2.nm_strumento_appl) ll) ff) vv;

CREATE VIEW v_clienti_almeno_debito_credito_1116_1017 AS
SELECT *
FROM
  (SELECT chcode,
          count(CASE WHEN ambito_carta = 'CRE' or ambito_carta = 'Credito' THEN 1
                    ELSE NULL
                END) numCre,
          count(CASE WHEN ambito_carta = 'PRE' or ambito_carta = 'Prepagata' THEN 1
                    ELSE NULL
                END) numPre,
          count(CASE WHEN ambito_carta = 'DEB' or ambito_carta = 'Debito' THEN 1
                    ELSE NULL
                END) numDEB
   FROM t_chcode_ambitocarta_1116_1017
   GROUP BY chcode
   ORDER BY chcode ASC) TEMP
WHERE numCre != 0
  AND numDeb != 0
ORDER BY chcode ASC;

CREATE VIEW v_clienti_almeno_debito_credito_prepagata_1116_1017 AS --todo
SELECT *
FROM
  (SELECT chcode,
          count(CASE WHEN ambito_carta = 'CRE' or ambito_carta = 'Credito' THEN 1
                    ELSE NULL
                END) numCre,
          count(CASE WHEN ambito_carta = 'PRE' or ambito_carta = 'Prepagata' THEN 1
                    ELSE NULL
                END) numPre,
          count(CASE WHEN ambito_carta = 'DEB' or ambito_carta = 'Debito' THEN 1
                    ELSE NULL
                END) numDEB
   FROM t_chcode_ambitocarta_1116_1017
   GROUP BY chcode
   ORDER BY chcode ASC) TEMP
WHERE numCre != 0
  AND numDeb != 0
  AND numPre != 0
ORDER BY chcode ASC;

CREATE VIEW v_clienti_solo_credito_1116_1017 AS
SELECT *
FROM
  (SELECT chcode,
          count(CASE WHEN ambito_carta = 'CRE' or ambito_carta = 'Credito' THEN 1
                    ELSE NULL
                END) numCre,
          count(CASE WHEN ambito_carta = 'PRE' or ambito_carta = 'Prepagata' THEN 1
                    ELSE NULL
                END) numPre,
          count(CASE WHEN ambito_carta = 'DEB' or ambito_carta = 'Debito' THEN 1
                    ELSE NULL
                END) numDEB
   FROM t_chcode_ambitocarta_1116_1017
   GROUP BY chcode
   ORDER BY chcode ASC) TEMP
WHERE numCre > 0
  AND numDeb = 0
  AND numPre = 0
ORDER BY chcode ASC;


CREATE VIEW v_clienti_solo_debito_1116_1017 AS
SELECT *
FROM
  (SELECT chcode,
          count(CASE WHEN ambito_carta = 'CRE' or ambito_carta = 'Credito' THEN 1
                    ELSE NULL
                END) numCre,
          count(CASE WHEN ambito_carta = 'PRE' or ambito_carta = 'Prepagata' THEN 1
                    ELSE NULL
                END) numPre,
          count(CASE WHEN ambito_carta = 'DEB' or ambito_carta = 'Debito' THEN 1
                    ELSE NULL
                END) numDEB
   FROM t_chcode_ambitocarta_1116_1017
   GROUP BY chcode
   ORDER BY chcode ASC) TEMP
WHERE numCre = 0
  AND numDeb > 0
  AND numPre = 0
ORDER BY chcode ASC;


CREATE VIEW v_clienti_solo_prepagate_1116_1017 AS
SELECT *
FROM
  (SELECT chcode,
          count(CASE WHEN ambito_carta = 'CRE' or ambito_carta = 'Credito' THEN 1
                    ELSE NULL
                END) numCre,
          count(CASE WHEN ambito_carta = 'PRE' or ambito_carta = 'Prepagata' THEN 1
                    ELSE NULL
                END) numPre,
          count(CASE WHEN ambito_carta = 'DEB' or ambito_carta = 'Debito' THEN 1
                    ELSE NULL
                END) numDEB
   FROM t_chcode_ambitocarta_1116_1017
   GROUP BY chcode
   ORDER BY chcode ASC) TEMP
WHERE numCre = 0
  AND numDeb = 0
  AND numPre > 0
ORDER BY chcode ASC;