WITH 
`CNCTR_F0` AS 
    (
    SELECT
        `MVC`.`CNTRP_EMT_ID`, 
        `MVC`.`PRTFGL_ID`, 
        `MVC`.`INV`, 
        `MVC`.`PTRM_OGG`, 
        `MVC`.`PTRM_SOGG`, 
        `MVC`.`DT_ASSG`, 
        `MVC`.`DT_SCDN`, 
        `MVC`.`PIVA_SGR`, 
        `MVC`.`DT_INS`, 
        `MVC`.`DT_AGG`, 
        `MVC`.`STM_SRG`, 
        `MVC`.`CD_AMB`, 
        `MVC`.`NUMERO_DT_PERIODO`, 
        `MVC`.`RTNG_ID`, 
        `MVC`.`OICR_SOGG_ID`, 
        `MVC`.`OICR_OGG_ID`
    FROM
        `level2`.`cnctr_f` `MVC`, 
        `level2`.`security` `SEC`, 
        (
        SELECT
            MAX(`DT_SCDN`) AS `MAX_SCD`, 
            `PRTFGL_ID`
        FROM
            `level2`.`cnctr_f` 
        GROUP BY 
            `PRTFGL_ID`
        ) `SCD`, 
        (
        SELECT
            MAX(`DT_ASSG`) AS `MAX_ASS`, 
            `PRTFGL_ID`
        FROM
            `level2`.`cnctr_f` 
        GROUP BY 
            `PRTFGL_ID`
        ) `ASS` 
    WHERE 
        `SEC`.`SEC_TYPE` IN ( 
            'SGR' ) AND
        `SEC`.`USER_GROUP` IN ( 
            'Amministratori Analytics', 
            'Amministratori di sistema', 
            'Cognos', 
            'Tutti', 
            'Tutti gli utenti autenticati', 
            'algorfin', 
            'cognosadm' ) AND
        
            CASE `SEC`.`SEC_TYPE`
                WHEN 'SGR' THEN `MVC`.`PIVA_SGR`
                ELSE '----'
            END LIKE `SEC`.`SEC_FILTER` AND
        `MVC`.`PRTFGL_ID` = `SCD`.`PRTFGL_ID` AND
        `MVC`.`DT_SCDN` = `SCD`.`MAX_SCD` AND
        `MVC`.`PRTFGL_ID` = `ASS`.`PRTFGL_ID` AND
        `MVC`.`DT_ASSG` = `ASS`.`MAX_ASS`
    ), 
`CN_PARAM` AS 
    (
    SELECT
        `MVC`.`PIVA_SGR`, 
        `MVC`.`SGR_NM`, 
        `MVC`.`LMT_DS_1`, 
        `MVC`.`PERC_1`, 
        `MVC`.`LMT_DS_2`, 
        `MVC`.`PERC_2`, 
        `MVC`.`LMT_DS_3`, 
        `MVC`.`PERC_3`, 
        `MVC`.`LOGO_INT`, 
        `MVC`.`LOGO_EST_1`, 
        `MVC`.`LOGO_EST_2`
    FROM
        `level2`.`param` `MVC`, 
        `level2`.`security` `SEC` 
    WHERE 
        `SEC`.`SEC_TYPE` IN ( 
            'SGR' ) AND
        `SEC`.`USER_GROUP` IN ( 
            'Amministratori Analytics', 
            'Amministratori di sistema', 
            'Cognos', 
            'Tutti', 
            'Tutti gli utenti autenticati', 
            'algorfin', 
            'cognosadm' ) AND
        
            CASE `SEC`.`SEC_TYPE`
                WHEN 'SGR' THEN `MVC`.`PIVA_SGR`
                ELSE '----'
            END LIKE `SEC`.`SEC_FILTER`
    ), 
`CN_PRTFGL_D` AS 
    (
    SELECT
        `MVC`.`PRTFGL_ID`, 
        `MVC`.`PRTFGL_DT`, 
        `MVC`.`PRTFGL_STR_FNZ_SOGG_TCKR`, 
        `MVC`.`PRTFGL_STR_FNZ_SOGG_DS`, 
        `MVC`.`PRTFGL_STR_FNZ_SOGG_FULL_NAME`, 
        `MVC`.`PRTFGL_STR_FNZ_OGG_TCKR`, 
        `MVC`.`PRTFGL_STR_FNZ_OGG_DS`, 
        `MVC`.`PRTFGL_STR_FNZ_OGG_FULL_NAME`, 
        `MVC`.`PIVA_SGR`, 
        `MVC`.`DT_INS`, 
        `MVC`.`DT_AGG`, 
        `MVC`.`STM_SRG`, 
        `MVC`.`CD_AMB`
    FROM
        `level2`.`prtfgl_d` `MVC`, 
        `level2`.`security` `SEC` 
    WHERE 
        `SEC`.`SEC_TYPE` IN ( 
            'SGR' ) AND
        `SEC`.`USER_GROUP` IN ( 
            'Amministratori Analytics', 
            'Amministratori di sistema', 
            'Cognos', 
            'Tutti', 
            'Tutti gli utenti autenticati', 
            'algorfin', 
            'cognosadm' ) AND
        
            CASE `SEC`.`SEC_TYPE`
                WHEN 'SGR' THEN `MVC`.`PIVA_SGR`
                ELSE '----'
            END LIKE `SEC`.`SEC_FILTER`
    ), 
`CN_OICR_TRZ_D` AS 
    (
    SELECT
        `MVC`.`OICR_ID`, 
        `MVC`.`OICR_CD_FND`, 
        `MVC`.`OICR_ID_BB_UNQ`, 
        `MVC`.`OICR_CD_TTL`, 
        `MVC`.`OICR_ISIN`, 
        `MVC`.`OICR_TCKR`, 
        `MVC`.`OICR_DS`, 
        `MVC`.`OICR_FULL_NAME`, 
        `MVC`.`OICR_CNTRP_EMT_NM`, 
        `MVC`.`OICR_STT_ATT_ECNM_CD`, 
        `MVC`.`OICR_STT_ATT_ECNM_DS`, 
        `MVC`.`OICR_CD_BRS_CD`, 
        `MVC`.`OICR_CD_BRS_NM`, 
        `MVC`.`OICR_CTG_VAL_MOB_CD`, 
        `MVC`.`OICR_CTG_VAL_MOB_DS_`, 
        `MVC`.`OICR_DVS_CD`, 
        `MVC`.`OICR_DVS_DS`, 
        `MVC`.`OICR_MSTR_CNT_CD`, 
        `MVC`.`OICR_MSTR_CNT_DS`, 
        `MVC`.`OICR_STR_FNZ_TP`, 
        `MVC`.`OICR_IND_OICR`, 
        `MVC`.`OICR_TP_FND`, 
        `MVC`.`OICR_DT_FN_VLDT`, 
        `MVC`.`OICR_FLG_CNTR`, 
        `MVC`.`OICR_CLSSF_LVL_1`, 
        `MVC`.`OICR_CLSSF_LVL_2`, 
        `MVC`.`OICR_CLSSF_LVL_3`, 
        `MVC`.`OICR_CLSSF_LVL_4`, 
        `MVC`.`OICR_CLSSF_LVL_5`, 
        `MVC`.`OICR_CLSSF_LVL_6`, 
        `MVC`.`OICR_CLSSF_LVL_7`, 
        `MVC`.`OICR_CLSSF_LVL_8`, 
        `MVC`.`OICR_CLSSF_LVL_9`, 
        `MVC`.`OICR_CLSSF_LVL_10`, 
        `MVC`.`OICR_CLSSF_LVL_11`, 
        `MVC`.`OICR_CLSSF_LVL_12`, 
        `MVC`.`OICR_CLSSF_LVL_13`, 
        `MVC`.`OICR_CLSSF_LVL_14`, 
        `MVC`.`OICR_CLSSF_LVL_15`, 
        `MVC`.`OICR_PRP_POL_GST`, 
        `MVC`.`OICR_PRP_PRTFGL_MNGR`, 
        `MVC`.`OICR_TRZ_FLG_UNV_INVB`, 
        `MVC`.`PIVA_SGR`, 
        `MVC`.`DT_INS`, 
        `MVC`.`DT_AGG`, 
        `MVC`.`STM_SRG`, 
        `MVC`.`CD_AMB`
    FROM
        `level2`.`oicr_d` `MVC`, 
        `level2`.`security` `SEC` 
    WHERE 
        `MVC`.`OICR_IND_OICR` = 'TERZI' AND
        `SEC`.`SEC_TYPE` IN ( 
            'SGR' ) AND
        `SEC`.`USER_GROUP` IN ( 
            'Amministratori Analytics', 
            'Amministratori di sistema', 
            'Cognos', 
            'Tutti', 
            'Tutti gli utenti autenticati', 
            'algorfin', 
            'cognosadm' ) AND
        
            CASE `SEC`.`SEC_TYPE`
                WHEN 'SGR' THEN `MVC`.`PIVA_SGR`
                ELSE '----'
            END LIKE `SEC`.`SEC_FILTER`
    ), 
`CN_RTNG_D` AS 
    (
    SELECT
        `MVC`.`RTNG_ID`, 
        `MVC`.`RTNG_NM`, 
        `MVC`.`RTNG_TP_DS`, 
        `MVC`.`DT_INIZIO_RIGA`, 
        `MVC`.`DT_FN_RIGA`, 
        `MVC`.`IND_CORRENTE`, 
        `MVC`.`RTNG_LMT_ID`, 
        `MVC`.`PIVA_SGR`, 
        `MVC`.`DT_INS`, 
        `MVC`.`DT_AGG`, 
        `MVC`.`STM_SRG`, 
        `MVC`.`CD_AMB`
    FROM
        `level2`.`rtng_d` `MVC`, 
        `level2`.`security` `SEC` 
    WHERE 
        `SEC`.`SEC_TYPE` IN ( 
            'SGR' ) AND
        `SEC`.`USER_GROUP` IN ( 
            'Amministratori Analytics', 
            'Amministratori di sistema', 
            'Cognos', 
            'Tutti', 
            'Tutti gli utenti autenticati', 
            'algorfin', 
            'cognosadm' ) AND
        
            CASE `SEC`.`SEC_TYPE`
                WHEN 'SGR' THEN `MVC`.`PIVA_SGR`
                ELSE '----'
            END LIKE `SEC`.`SEC_FILTER`
    ), 
`CN_RTNG_LMT` AS 
    (
    SELECT
        `MVC`.`RTNG_LMT_ID`, 
        `MVC`.`RTNG_NM`, 
        `MVC`.`POL_GST`, 
        `MVC`.`RTNG_TP_DS`, 
        `MVC`.`PERC`, 
        `MVC`.`DT_INIZIO`, 
        `MVC`.`DT_FN`, 
        `MVC`.`IND_CORRENTE`, 
        `MVC`.`PIVA_SGR`, 
        `MVC`.`DT_INS`, 
        `MVC`.`DT_AGG`, 
        `MVC`.`STM_SRG`, 
        `MVC`.`CD_AMB`
    FROM
        `level2`.`rtng_lmt` `MVC`, 
        `level2`.`security` `SEC` 
    WHERE 
        `SEC`.`SEC_TYPE` IN ( 
            'SGR' ) AND
        `SEC`.`USER_GROUP` IN ( 
            'Amministratori Analytics', 
            'Amministratori di sistema', 
            'Cognos', 
            'Tutti', 
            'Tutti gli utenti autenticati', 
            'algorfin', 
            'cognosadm' ) AND
        
            CASE `SEC`.`SEC_TYPE`
                WHEN 'SGR' THEN `MVC`.`PIVA_SGR`
                ELSE '----'
            END LIKE `SEC`.`SEC_FILTER`
    ), 
`CN_OICR_PRP_D` AS 
    (
    SELECT
        `MVC`.`OICR_ID`, 
        `MVC`.`OICR_CD_FND`, 
        `MVC`.`OICR_ID_BB_UNQ`, 
        `MVC`.`OICR_CD_TTL`, 
        `MVC`.`OICR_ISIN`, 
        `MVC`.`OICR_TCKR`, 
        `MVC`.`OICR_DS`, 
        `MVC`.`OICR_FULL_NAME`, 
        `MVC`.`OICR_CNTRP_EMT_NM`, 
        `MVC`.`OICR_STT_ATT_ECNM_CD`, 
        `MVC`.`OICR_STT_ATT_ECNM_DS`, 
        `MVC`.`OICR_CD_BRS_CD`, 
        `MVC`.`OICR_CD_BRS_NM`, 
        `MVC`.`OICR_CTG_VAL_MOB_CD`, 
        `MVC`.`OICR_CTG_VAL_MOB_DS_`, 
        `MVC`.`OICR_DVS_CD`, 
        `MVC`.`OICR_DVS_DS`, 
        `MVC`.`OICR_MSTR_CNT_CD`, 
        `MVC`.`OICR_MSTR_CNT_DS`, 
        `MVC`.`OICR_STR_FNZ_TP`, 
        `MVC`.`OICR_IND_OICR`, 
        `MVC`.`OICR_TP_FND`, 
        `MVC`.`OICR_DT_FN_VLDT`, 
        `MVC`.`OICR_FLG_CNTR`, 
        `MVC`.`OICR_CLSSF_LVL_1`, 
        `MVC`.`OICR_CLSSF_LVL_2`, 
        `MVC`.`OICR_CLSSF_LVL_3`, 
        `MVC`.`OICR_CLSSF_LVL_4`, 
        `MVC`.`OICR_CLSSF_LVL_5`, 
        `MVC`.`OICR_CLSSF_LVL_6`, 
        `MVC`.`OICR_CLSSF_LVL_7`, 
        `MVC`.`OICR_CLSSF_LVL_8`, 
        `MVC`.`OICR_CLSSF_LVL_9`, 
        `MVC`.`OICR_CLSSF_LVL_10`, 
        `MVC`.`OICR_CLSSF_LVL_11`, 
        `MVC`.`OICR_CLSSF_LVL_12`, 
        `MVC`.`OICR_CLSSF_LVL_13`, 
        `MVC`.`OICR_CLSSF_LVL_14`, 
        `MVC`.`OICR_CLSSF_LVL_15`, 
        `MVC`.`OICR_PRP_POL_GST`, 
        `MVC`.`OICR_PRP_PRTFGL_MNGR`, 
        `MVC`.`OICR_TRZ_FLG_UNV_INVB`, 
        `MVC`.`PIVA_SGR`, 
        `MVC`.`DT_INS`, 
        `MVC`.`DT_AGG`, 
        `MVC`.`STM_SRG`, 
        `MVC`.`CD_AMB`
    FROM
        `level2`.`oicr_d` `MVC`, 
        `level2`.`security` `SEC` 
    WHERE 
        `MVC`.`OICR_IND_OICR` = 'PROPRI' AND
        `SEC`.`SEC_TYPE` IN ( 
            'SGR' ) AND
        `SEC`.`USER_GROUP` IN ( 
            'Amministratori Analytics', 
            'Amministratori di sistema', 
            'Cognos', 
            'Tutti', 
            'Tutti gli utenti autenticati', 
            'algorfin', 
            'cognosadm' ) AND
        
            CASE `SEC`.`SEC_TYPE`
                WHEN 'SGR' THEN `MVC`.`PIVA_SGR`
                ELSE '----'
            END LIKE `SEC`.`SEC_FILTER`
    ), 
`TQ0_Fatti` AS 
    (
    SELECT
        `CN_OICR_PRP_D`.`OICR_TP_FND` AS `OICR_Propri_Tipo_Fondo`, 
        `CN_OICR_PRP_D`.`OICR_FULL_NAME` AS `OICR_Propri_Full_Name`, 
        `CN_OICR_PRP_D`.`OICR_PRP_POL_GST` AS `OICR_Propri_Politica_Gestione`, 
        `CN_OICR_PRP_D`.`OICR_PRP_PRTFGL_MNGR` AS `OICR_Propri_Portafoglio_Manager`, 
        `CN_PARAM`.`PERC_2` AS `PERC_2`, 
        `CN_PARAM`.`PERC_1` AS `PERC_1`, 
        `CN_RTNG_D`.`RTNG_NM` AS `RTNG_NM`, 
        `CNCTR_F0`.`INV` AS `INV`, 
        `CN_OICR_TRZ_D`.`OICR_CLSSF_LVL_1` AS `OICR_CLSSF_LVL_1`, 
        `CN_OICR_PRP_D`.`OICR_ID` AS `OICR_ID`, 
        `CN_OICR_TRZ_D`.`OICR_ID` AS `OICR_ID1`, 
        `CN_OICR_TRZ_D`.`OICR_TP_FND` AS `OICR_TP_FND`, 
        CASE 
            WHEN 
                SUM(1)
                    OVER(
                        PARTITION BY
                            `CN_OICR_PRP_D`.`OICR_ID`, 
                            `CN_OICR_TRZ_D`.`OICR_ID`
                        ORDER BY
                            `CN_OICR_PRP_D`.`OICR_ID` ASC, 
                            `CN_OICR_TRZ_D`.`OICR_ID` ASC
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) = 1
                THEN
                    `CN_OICR_TRZ_D`.`OICR_ID`
            ELSE NULL
        END AS `C__patrimonio_OICR_in_universo_investibile_Desc_d`, 
        CASE 
            WHEN 
                SUM(1)
                    OVER(
                        PARTITION BY
                            `CN_OICR_PRP_D`.`OICR_ID`, 
                            CASE 
                                WHEN `CN_OICR_TRZ_D`.`OICR_TP_FND` = 'ETF' THEN `CN_OICR_TRZ_D`.`OICR_ID`
                                ELSE NULL
                            END
                        ORDER BY
                            `CN_OICR_PRP_D`.`OICR_ID` ASC, 
                            CASE 
                                WHEN `CN_OICR_TRZ_D`.`OICR_TP_FND` = 'ETF' THEN `CN_OICR_TRZ_D`.`OICR_ID`
                                ELSE NULL
                            END ASC
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) = 1 AND
                `CN_OICR_TRZ_D`.`OICR_TP_FND` = 'ETF'
                THEN
                    `CN_OICR_TRZ_D`.`OICR_ID`
            ELSE NULL
        END AS `C__patrimonio_OICR_in_universo_investibile_Desc1_d`, 
        CASE 
            WHEN 
                SUM(1)
                    OVER(
                        PARTITION BY
                            `CN_OICR_PRP_D`.`OICR_TP_FND`, 
                            `CN_OICR_PRP_D`.`OICR_FULL_NAME`, 
                            `CN_OICR_PRP_D`.`OICR_PRP_POL_GST`, 
                            `CN_OICR_PRP_D`.`OICR_PRP_PRTFGL_MNGR`, 
                            `CN_PARAM`.`PIVA_SGR`
                        ORDER BY
                            `CN_OICR_PRP_D`.`OICR_TP_FND` ASC, 
                            `CN_OICR_PRP_D`.`OICR_FULL_NAME` ASC, 
                            `CN_OICR_PRP_D`.`OICR_PRP_POL_GST` ASC, 
                            `CN_OICR_PRP_D`.`OICR_PRP_PRTFGL_MNGR` ASC, 
                            `CN_PARAM`.`PIVA_SGR` ASC
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) = 1
                THEN
                    `CN_PARAM`.`PERC_2`
            ELSE NULL
        END AS `PERC_2_u`, 
        CASE 
            WHEN 
                SUM(1)
                    OVER(
                        PARTITION BY
                            `CN_OICR_PRP_D`.`OICR_TP_FND`, 
                            `CN_OICR_PRP_D`.`OICR_FULL_NAME`, 
                            `CN_OICR_PRP_D`.`OICR_PRP_POL_GST`, 
                            `CN_OICR_PRP_D`.`OICR_PRP_PRTFGL_MNGR`, 
                            `CN_PARAM`.`PIVA_SGR`
                        ORDER BY
                            `CN_OICR_PRP_D`.`OICR_TP_FND` ASC, 
                            `CN_OICR_PRP_D`.`OICR_FULL_NAME` ASC, 
                            `CN_OICR_PRP_D`.`OICR_PRP_POL_GST` ASC, 
                            `CN_OICR_PRP_D`.`OICR_PRP_PRTFGL_MNGR` ASC, 
                            `CN_PARAM`.`PIVA_SGR` ASC
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) = 1
                THEN
                    `CN_PARAM`.`PERC_1`
            ELSE NULL
        END AS `PERC_1_u`, 
        CASE 
            WHEN 
                SUM(1)
                    OVER(
                        PARTITION BY
                            `CN_PARAM`.`PIVA_SGR`
                        ORDER BY
                            `CN_PARAM`.`PIVA_SGR` ASC
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) = 1
                THEN
                    `CN_PARAM`.`PERC_1`
            ELSE NULL
        END AS `PERC_1_u1`, 
        CASE 
            WHEN 
                SUM(1)
                    OVER(
                        PARTITION BY
                            `CN_OICR_PRP_D`.`OICR_TP_FND`, 
                            `CN_PARAM`.`PIVA_SGR`
                        ORDER BY
                            `CN_OICR_PRP_D`.`OICR_TP_FND` ASC, 
                            `CN_PARAM`.`PIVA_SGR` ASC
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                    ) = 1
                THEN
                    `CN_PARAM`.`PERC_2`
            ELSE NULL
        END AS `PERC_2_u1`
    FROM
        `CNCTR_F0`
            INNER JOIN `CN_PARAM`
            ON `CNCTR_F0`.`PIVA_SGR` = `CN_PARAM`.`PIVA_SGR`
                INNER JOIN `CN_PRTFGL_D`
                ON `CNCTR_F0`.`PRTFGL_ID` = `CN_PRTFGL_D`.`PRTFGL_ID`
                    LEFT OUTER JOIN `CN_OICR_TRZ_D`
                    ON `CN_OICR_TRZ_D`.`OICR_ID` = `CNCTR_F0`.`OICR_OGG_ID`
                        LEFT OUTER JOIN 
                        (`CN_RTNG_D`
                            INNER JOIN `CN_RTNG_LMT`
                            ON `CN_RTNG_D`.`RTNG_LMT_ID` = `CN_RTNG_LMT`.`RTNG_LMT_ID`)
                        ON `CNCTR_F0`.`RTNG_ID` = `CN_RTNG_D`.`RTNG_ID`
                            LEFT OUTER JOIN `CN_OICR_PRP_D`
                            ON `CNCTR_F0`.`OICR_SOGG_ID` = `CN_OICR_PRP_D`.`OICR_ID` 
    WHERE 
        `CN_PRTFGL_D`.`PRTFGL_DT` = 20171110 AND
        `CN_OICR_PRP_D`.`PIVA_SGR` = '11969870150' AND
        `CN_OICR_PRP_D`.`OICR_TP_FND` <> 'ETF' AND
        `CN_OICR_TRZ_D`.`OICR_TP_FND` <> 'ETF' AND
        (`CN_RTNG_D`.`IND_CORRENTE` = 'C' OR
        `CN_RTNG_D`.`IND_CORRENTE` IS NULL) AND
        (`CN_OICR_PRP_D`.`OICR_PRP_POL_GST` = `CN_RTNG_LMT`.`POL_GST` OR
        `CN_RTNG_D`.`IND_CORRENTE` IS NULL)
    ), 
`SQ3_Fatti` AS 
    (
    SELECT
        `TQ0_Fatti`.`OICR_Propri_Tipo_Fondo` AS `OICR_Propri_Tipo_Fondo`, 
        `TQ0_Fatti`.`OICR_Propri_Full_Name` AS `OICR_Propri_Full_Name`, 
        `TQ0_Fatti`.`OICR_Propri_Politica_Gestione` AS `OICR_Propri_Politica_Gestione`, 
        `TQ0_Fatti`.`OICR_Propri_Portafoglio_Manager` AS `OICR_Propri_Portafoglio_Manager`, 
        `TQ0_Fatti`.`OICR_ID` AS `OICR_ID`, 
        SUM(`TQ0_Fatti`.`PERC_2_u` / 100) AS `Sum1`, 
        SUM(`TQ0_Fatti`.`PERC_1_u` / 100) AS `Sum11`, 
        SUM(
            CASE 
                WHEN 
                    `TQ0_Fatti`.`RTNG_NM` IN ( 
                        'GREEN', 
                        'AMBER' ) AND
                    `TQ0_Fatti`.`OICR_Propri_Tipo_Fondo` <> 'ETF'
                    THEN
                        `TQ0_Fatti`.`INV`
                ELSE 0
            END) AS `Sum12`, 
        SUM(
            CASE 
                WHEN `TQ0_Fatti`.`OICR_Propri_Tipo_Fondo` <> 'ETF' THEN `TQ0_Fatti`.`INV`
                ELSE 0
            END) AS `Sum13`, 
        SUM(`TQ0_Fatti`.`PERC_1_u1` / 100) AS `Sum14`, 
        SUM(
            CASE 
                WHEN 
                    `TQ0_Fatti`.`RTNG_NM` IN ( 
                        'GREEN' ) AND
                    `TQ0_Fatti`.`OICR_CLSSF_LVL_1` = 'Flessibile' AND
                    `TQ0_Fatti`.`OICR_Propri_Tipo_Fondo` <> 'ETF' OR
                    `TQ0_Fatti`.`RTNG_NM` IN ( 
                        'AMBER' ) AND
                    `TQ0_Fatti`.`OICR_CLSSF_LVL_1` = 'Flessibile' AND
                    `TQ0_Fatti`.`OICR_Propri_Tipo_Fondo` <> 'ETF'
                    THEN
                        `TQ0_Fatti`.`INV`
                ELSE 0
            END) AS `Sum15`, 
        SUM(
            CASE 
                WHEN 
                    `TQ0_Fatti`.`OICR_CLSSF_LVL_1` = 'Flessibile' AND
                    `TQ0_Fatti`.`OICR_Propri_Tipo_Fondo` <> 'ETF'
                    THEN
                        `TQ0_Fatti`.`INV`
                ELSE 0
            END) AS `Sum16`, 
        SUM(`TQ0_Fatti`.`PERC_2_u1` / 100) AS `Sum17`, 
        COUNT(`TQ0_Fatti`.`C__patrimonio_OICR_in_universo_investibile_Desc_d`) AS `Count1`, 
        COUNT(`TQ0_Fatti`.`C__patrimonio_OICR_in_universo_investibile_Desc1_d`) AS `Count11`
    FROM
        `TQ0_Fatti` 
    GROUP BY 
        `TQ0_Fatti`.`OICR_Propri_Tipo_Fondo`, 
        `TQ0_Fatti`.`OICR_Propri_Full_Name`, 
        `TQ0_Fatti`.`OICR_Propri_Politica_Gestione`, 
        `TQ0_Fatti`.`OICR_Propri_Portafoglio_Manager`, 
        `TQ0_Fatti`.`OICR_ID`
    )
SELECT DISTINCT 
    `SQ3_Fatti`.`OICR_Propri_Tipo_Fondo` AS `OICR_Propri_Tipo_Fondo`, 
    SUM(`SQ3_Fatti`.`Sum12`)
        OVER(
        ) / NULLIF(SUM(`SQ3_Fatti`.`Sum13`)
        OVER(
        ), 0) AS `Tot___patrimonio_in_Universo_Inv`, 
    SUM(`SQ3_Fatti`.`Sum14`)
        OVER(
        ) AS `Percentuale_Limite_1`, 
    SUM(`SQ3_Fatti`.`Sum15`)
        OVER(
        ) / NULLIF(SUM(`SQ3_Fatti`.`Sum16`)
        OVER(
        ), 0) AS `Tot___patrimonio_Rid_in_Universo_Inv`, 
    `SQ3_Fatti`.`OICR_Propri_Full_Name` AS `OICR_Propri_Full_Name`, 
    `SQ3_Fatti`.`OICR_Propri_Politica_Gestione` AS `OICR_Propri_Politica_Gestione`, 
    `SQ3_Fatti`.`OICR_Propri_Portafoglio_Manager` AS `OICR_Propri_Portafoglio_Manager`, 
    CASE 
        WHEN 
            SUM(`SQ3_Fatti`.`Count1`)
                OVER(
                    PARTITION BY
                        `SQ3_Fatti`.`OICR_ID`
                ) <> SUM(`SQ3_Fatti`.`Count11`)
                OVER(
                    PARTITION BY
                        `SQ3_Fatti`.`OICR_ID`
                )
            THEN
                CASE 
                    WHEN 
                        CASE 
                            WHEN 
                                SUM(`SQ3_Fatti`.`Count1`)
                                    OVER(
                                        PARTITION BY
                                            `SQ3_Fatti`.`OICR_ID`
                                    ) <> SUM(`SQ3_Fatti`.`Count11`)
                                    OVER(
                                        PARTITION BY
                                            `SQ3_Fatti`.`OICR_ID`
                                    )
                                THEN
                                    SUM(`SQ3_Fatti`.`Sum12`)
                                        OVER(
                                            PARTITION BY
                                                `SQ3_Fatti`.`OICR_Propri_Full_Name`
                                        ) / NULLIF(SUM(`SQ3_Fatti`.`Sum13`)
                                        OVER(
                                            PARTITION BY
                                                `SQ3_Fatti`.`OICR_Propri_Full_Name`
                                        ), 0)
                            ELSE NULL
                        END < SUM(`SQ3_Fatti`.`Sum1`)
                            OVER(
                                PARTITION BY
                                    `SQ3_Fatti`.`OICR_Propri_Tipo_Fondo`, 
                                    `SQ3_Fatti`.`OICR_Propri_Full_Name`, 
                                    `SQ3_Fatti`.`OICR_Propri_Politica_Gestione`, 
                                    `SQ3_Fatti`.`OICR_Propri_Portafoglio_Manager`
                            )
                        THEN
                            '▼'
                    ELSE '✓'
                END
        ELSE NULL
    END AS `C__patrimonio_OICR_in_universo_investibile_Desc`, 
    CASE 
        WHEN 
            SUM(`SQ3_Fatti`.`Count1`)
                OVER(
                    PARTITION BY
                        `SQ3_Fatti`.`OICR_ID`
                ) <> SUM(`SQ3_Fatti`.`Count11`)
                OVER(
                    PARTITION BY
                        `SQ3_Fatti`.`OICR_ID`
                )
            THEN
                SUM(`SQ3_Fatti`.`Sum12`)
                    OVER(
                        PARTITION BY
                            `SQ3_Fatti`.`OICR_Propri_Full_Name`
                    ) / NULLIF(SUM(`SQ3_Fatti`.`Sum13`)
                    OVER(
                        PARTITION BY
                            `SQ3_Fatti`.`OICR_Propri_Full_Name`
                    ), 0)
        ELSE NULL
    END AS `C__patrimonio_OICR_in_universo_investibile`, 
    SUM(`SQ3_Fatti`.`Sum11`)
        OVER(
            PARTITION BY
                `SQ3_Fatti`.`OICR_Propri_Tipo_Fondo`, 
                `SQ3_Fatti`.`OICR_Propri_Full_Name`, 
                `SQ3_Fatti`.`OICR_Propri_Politica_Gestione`, 
                `SQ3_Fatti`.`OICR_Propri_Portafoglio_Manager`
        ) AS `Percentuale_Limite_11`, 
    SUM(`SQ3_Fatti`.`Sum1`)
        OVER(
            PARTITION BY
                `SQ3_Fatti`.`OICR_Propri_Tipo_Fondo`, 
                `SQ3_Fatti`.`OICR_Propri_Full_Name`, 
                `SQ3_Fatti`.`OICR_Propri_Politica_Gestione`, 
                `SQ3_Fatti`.`OICR_Propri_Portafoglio_Manager`
        ) AS `Percentuale_Limite_2`, 
    MOD(COUNT(1)
        OVER(
            ORDER BY
                `SQ3_Fatti`.`OICR_Propri_Tipo_Fondo` ASC, 
                `SQ3_Fatti`.`OICR_Propri_Full_Name` ASC, 
                `SQ3_Fatti`.`OICR_Propri_Politica_Gestione` ASC, 
                `SQ3_Fatti`.`OICR_Propri_Portafoglio_Manager` ASC
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ), 2) AS `Alternate_Rows`, 
    SUM(`SQ3_Fatti`.`Count1`)
        OVER(
            PARTITION BY
                `SQ3_Fatti`.`OICR_ID`
        ) AS `Num_OICR_Terzi`, 
    SUM(`SQ3_Fatti`.`Count11`)
        OVER(
            PARTITION BY
                `SQ3_Fatti`.`OICR_ID`
        ) AS `Num_OICR_Terzi_ETF`, 
    SUM(`SQ3_Fatti`.`Sum17`)
        OVER(
            PARTITION BY
                `SQ3_Fatti`.`OICR_Propri_Tipo_Fondo`
        ) AS `Percentuale_Limite_21`
FROM
    `SQ3_Fatti` 
ORDER BY 
    `OICR_Propri_Tipo_Fondo` ASC, 
    `OICR_Propri_Full_Name` ASC, 
    `OICR_Propri_Politica_Gestione` ASC, 
    `OICR_Propri_Portafoglio_Manager` ASC