Normalized Query
select `STRUCTURE_EU`.`N1_ORG_ID` as `c0`, `STRUCTURE_EU`.`ORG_SIREN_ENSEIGNE` as `c1`, `STRUCTURE_EU`.`N2_ORG_ID` as `c2`, `STRUCTURE_ETT`.`N1_ORG_ID` as `c3`, `STRUCTURE_ETT`.`ORG_SIREN_ETT` as `c4`, `DATE_ARCHI`.`ANNEE` as `c5`, `DATE_ARCHI`.`TRIMESTRE` as `c6`, `DATE_ARCHI`.`MOIS` as `c7`, `TYPE_CTR`.`REF_LIBELLE` as `c8`, count(distinct IF(FAIT_ASSIGNMENTS.ASS_CONTRACTVERSION NOT LIKE ?, CONCAT(FAIT_ASSIGNMENTS.ASS_ID, ?,FAIT_ASSIGNMENTS.ORIGINE),NULL)) as `m0`, count(distinct IF(FAIT_ASSIGNMENTS.CTR_EST_ARCHIVE = ? AND FAIT_ASSIGNMENTS.ASS_CONTRACTVERSION NOT LIKE ? , FAIT_ASSIGNMENTS.ASS_ID, NULL)) as `m1`, count(distinct IF(CTR_EST_ARCHIVE_ETT = ? AND FAIT_ASSIGNMENTS.ASS_CONTRACTVERSION NOT LIKE ?, FAIT_ASSIGNMENTS.ASS_ID, NULL)) as `m2`, sum(`FAIT_ASSIGNMENTS`.`DEMANDE_MODIF_NBR`) as `m3`, sum(`FAIT_ASSIGNMENTS`.`MODIF_NBR`) as `m4`, count(distinct IF(CTR_A_RAPPRO_OK = ? AND ASS_CONTRACTVERSION NOT LIKE ?, FAIT_ASSIGNMENTS.ASS_ID, NULL)) as `m5`, count(distinct IF(CTR_A_RAPPRO_KO = ? AND ASS_CONTRACTVERSION NOT LIKE ?, FAIT_ASSIGNMENTS.ASS_ID, NULL)) as `m6`, count(distinct IF(CTR_EST_SANS_RAPPRO = ? AND ASS_CONTRACTVERSION NOT LIKE ?, FAIT_ASSIGNMENTS.ASS_ID, NULL)) as `m7` from `dm_master`.`dim_structure` as `STRUCTURE_EU`, `dm_fait`.`fait_assignments` as `FAIT_ASSIGNMENTS`, `dm_master`.`dim_structure` as `STRUCTURE_ETT`, `dm_master`.`dim_date` as `DATE_ARCHI`, (SELECT TCT.REF_LIBELLE , IF( ASS.ASS_CONTRACTVERSION LIKE ? , ? , TCT.ID_REF ) AS ID_REF , TCT.ORDRE , ASS.ASS_ID FROM dm_master.DIM_REFERENCE AS TCT RIGHT JOIN dm_fait.fait_assignments AS ASS ON TCT.ID_REF = ASS.ASS_TYPE_DOCUMENT WHERE TCT.TYPE LIKE ? AND ASS.ENRG_A_SUPP = ? AND TCT.CODE_LANGUE IN (?) ORDER BY TCT.ORDRE) as `TYPE_CTR` where (STRUCTURE_EU.ORG_ID_STRUCTURE IN (SELECT ORG_ID_EU FROM dm_master.dim_maillage_doc WHERE ORG_ID_EU IN (SELECT ORG_ID FROM dm_master.uti_from_to a INNER JOIN dm_master.uti_uti_org b ON b.UTI_ID = a.UTI_ID_TO WHERE CAST(a.UTI_ID_FROM AS CHAR(?)) = ? ))) and (FAIT_ASSIGNMENTS.ENRG_A_SUPP = ? AND FAIT_ASSIGNMENTS.ORG_ID_STRUCTURE_EU IN (SELECT ORG_ID FROM dm_master.uti_from_to a INNER JOIN dm_master.uti_uti_org b ON b.UTI_ID = a.UTI_ID_TO WHERE CAST(a.UTI_ID_FROM AS CHAR(?)) = ? )) and `FAIT_ASSIGNMENTS`.`ORG_ID_STRUCTURE_EU` = `STRUCTURE_EU`.`ORG_ID_STRUCTURE` and `STRUCTURE_EU`.`N1_ORG_ID` = ? and `STRUCTURE_EU`.`ORG_SIREN_ENSEIGNE` = ? and `STRUCTURE_EU`.`N2_ORG_ID` = ? and (STRUCTURE_ETT.ORG_ID_STRUCTURE IN (SELECT ORG_ID_ETT FROM dm_master.dim_maillage_doc WHERE ORG_ID_EU IN (SELECT ORG_ID FROM dm_master.uti_from_to a INNER JOIN dm_master.uti_uti_org b ON b.UTI_ID = a.UTI_ID_TO WHERE CAST(a.UTI_ID_FROM AS CHAR(?)) = ? ))) and `FAIT_ASSIGNMENTS`.`ORG_ID_STRUCTURE_ETT` = `STRUCTURE_ETT`.`ORG_ID_STRUCTURE` and `STRUCTURE_ETT`.`N1_ORG_ID` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and (`STRUCTURE_ETT`.`ORG_SIREN_ETT` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) or `STRUCTURE_ETT`.`ORG_SIREN_ETT` is null) and (DATE(DATE_ARCHI.DATE_ID) BETWEEN ? AND DATE_ADD(DATE(NOW()), INTERVAL ? YEAR) OR DATE(DATE_ARCHI.DATE_ID)=?) and `FAIT_ASSIGNMENTS`.`DATE_ARCHIVAGE` = `DATE_ARCHI`.`DATE_NUM` and `DATE_ARCHI`.`ANNEE` in (?, ?) and `DATE_ARCHI`.`TRIMESTRE` in (?, ?, ?, ?, ?) and `DATE_ARCHI`.`MOIS` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and `FAIT_ASSIGNMENTS`.`ASS_ID` = `TYPE_CTR`.`ASS_ID` group by `STRUCTURE_EU`.`N1_ORG_ID`, `STRUCTURE_EU`.`ORG_SIREN_ENSEIGNE`, `STRUCTURE_EU`.`N2_ORG_ID`, `STRUCTURE_ETT`.`N1_ORG_ID`, `STRUCTURE_ETT`.`ORG_SIREN_ETT`, `DATE_ARCHI`.`ANNEE`, `DATE_ARCHI`.`TRIMESTRE`, `DATE_ARCHI`.`MOIS`, `TYPE_CTR`.`REF_LIBELLE`
Example Query
select `STRUCTURE_EU`.`N1_ORG_ID` as `c0`, `STRUCTURE_EU`.`ORG_SIREN_ENSEIGNE` as `c1`, `STRUCTURE_EU`.`N2_ORG_ID` as `c2`, `STRUCTURE_ETT`.`N1_ORG_ID` as `c3`, `STRUCTURE_ETT`.`ORG_SIREN_ETT` as `c4`, `DATE_ARCHI`.`ANNEE` as `c5`, `DATE_ARCHI`.`TRIMESTRE` as `c6`, `DATE_ARCHI`.`MOIS` as `c7`, `TYPE_CTR`.`REF_LIBELLE` as `c8`, count(distinct IF(FAIT_ASSIGNMENTS.ASS_CONTRACTVERSION NOT LIKE '%AN', CONCAT(FAIT_ASSIGNMENTS.ASS_ID, '-',FAIT_ASSIGNMENTS.ORIGINE),NULL)) as `m0`, count(distinct IF(FAIT_ASSIGNMENTS.CTR_EST_ARCHIVE = 1 AND FAIT_ASSIGNMENTS.ASS_CONTRACTVERSION NOT LIKE '%AN' , FAIT_ASSIGNMENTS.ASS_ID, NULL)) as `m1`, count(distinct IF(CTR_EST_ARCHIVE_ETT = 1 AND FAIT_ASSIGNMENTS.ASS_CONTRACTVERSION NOT LIKE '%AN', FAIT_ASSIGNMENTS.ASS_ID, NULL)) as `m2`, sum(`FAIT_ASSIGNMENTS`.`DEMANDE_MODIF_NBR`) as `m3`, sum(`FAIT_ASSIGNMENTS`.`MODIF_NBR`) as `m4`, count(distinct IF(CTR_A_RAPPRO_OK = 1 AND ASS_CONTRACTVERSION NOT LIKE '%AN', FAIT_ASSIGNMENTS.ASS_ID, NULL)) as `m5`, count(distinct IF(CTR_A_RAPPRO_KO = 1 AND ASS_CONTRACTVERSION NOT LIKE '%AN', FAIT_ASSIGNMENTS.ASS_ID, NULL)) as `m6`, count(distinct IF(CTR_EST_SANS_RAPPRO = 1 AND ASS_CONTRACTVERSION NOT LIKE '%AN', FAIT_ASSIGNMENTS.ASS_ID, NULL)) as `m7` from `dm_master`.`dim_structure` as `STRUCTURE_EU`, `dm_fait`.`fait_assignments` as `FAIT_ASSIGNMENTS`, `dm_master`.`dim_structure` as `STRUCTURE_ETT`, `dm_master`.`dim_date` as `DATE_ARCHI`, (SELECT TCT.REF_LIBELLE , IF( ASS.ASS_CONTRACTVERSION LIKE '%AN' , 'A' , TCT.ID_REF ) AS ID_REF , TCT.ORDRE , ASS.ASS_ID FROM dm_master.DIM_REFERENCE AS TCT RIGHT JOIN dm_fait.fait_assignments AS ASS ON TCT.ID_REF = ASS.ASS_TYPE_DOCUMENT WHERE TCT.TYPE LIKE 'TCT' AND ASS.ENRG_A_SUPP = 0 AND TCT.CODE_LANGUE IN (1) ORDER BY TCT.ORDRE) as `TYPE_CTR` where (STRUCTURE_EU.ORG_ID_STRUCTURE IN (SELECT ORG_ID_EU FROM dm_master.dim_maillage_doc WHERE ORG_ID_EU IN (SELECT ORG_ID FROM dm_master.uti_from_to a INNER JOIN dm_master.uti_uti_org b ON b.UTI_ID = a.UTI_ID_TO WHERE CAST(a.UTI_ID_FROM AS CHAR(10)) = '511445' ))) and (FAIT_ASSIGNMENTS.ENRG_A_SUPP = 0 AND FAIT_ASSIGNMENTS.ORG_ID_STRUCTURE_EU IN (SELECT ORG_ID FROM dm_master.uti_from_to a INNER JOIN dm_master.uti_uti_org b ON b.UTI_ID = a.UTI_ID_TO WHERE CAST(a.UTI_ID_FROM AS CHAR(10)) = '511445' )) and `FAIT_ASSIGNMENTS`.`ORG_ID_STRUCTURE_EU` = `STRUCTURE_EU`.`ORG_ID_STRUCTURE` and `STRUCTURE_EU`.`N1_ORG_ID` = 143889 and `STRUCTURE_EU`.`ORG_SIREN_ENSEIGNE` = '380448944' and `STRUCTURE_EU`.`N2_ORG_ID` = 319437 and (STRUCTURE_ETT.ORG_ID_STRUCTURE IN (SELECT ORG_ID_ETT FROM dm_master.dim_maillage_doc WHERE ORG_ID_EU IN (SELECT ORG_ID FROM dm_master.uti_from_to a INNER JOIN dm_master.uti_uti_org b ON b.UTI_ID = a.UTI_ID_TO WHERE CAST(a.UTI_ID_FROM AS CHAR(10)) = '511445' ))) and `FAIT_ASSIGNMENTS`.`ORG_ID_STRUCTURE_ETT` = `STRUCTURE_ETT`.`ORG_ID_STRUCTURE` and `STRUCTURE_ETT`.`N1_ORG_ID` in (4002, 4003, 4005, 4014, 4054, 4142, 4322, 4382, 7202, 7222, 7304, 7306, 7542, 7568, 7762, 8276, 8616, 9456, 11537, 13594, 14159, 17189, 17992, 21008, 26931, 28815, 29930, 29994, 37480, 38784, 47822, 51513, 54874, 67188, 68483, 74004, 76986, 77713, 85241, 86043, 98047, 98091, 99059, 110918, 115267, 117813, 127241, 133274, 137743, 138904, 141770, 144662, 147942, 149996, 152036, 153079, 153975, 156392, 163691, 171834, 173120, 174098, 177147, 178323, 179683, 180215, 182851, 184809, 197368, 205864, 208847, 209565, 210668, 211733, 212633, 222671, 227934, 234105, 235695, 235706, 238088, 246663, 250018, 250212, 250945, 250948, 255528, 256286, 267971, 273994, 278380, 278888, 282846, 285697, 292567, 300380, 301597, 302020, 306483, 307068, 325910, 330566, 332079, 339037, 350597, 358895, 358904, 364605, 374418, 379814, 419802, 420281, 440662, 597126, 621393, 621735, 633969, 773095, 794065, 996728, 1032372, 1131559, 1133855, 1217586) and (`STRUCTURE_ETT`.`ORG_SIREN_ETT` in ('09S05125', '301273447', '303877153', '304381379', '311801112', '314091927', '314887126', '319861597', '325742740', '329925010', '337080543', '339993164', '354044497', '354088544', '381680289', '382643955', '383497963', '384964508', '385052618', '389854878', '391498565', '393923636', '394543433', '394853790', '397780933', '399157650', '399256890', '401015938', '402133243', '403140429', '407655471', '407666502', '407759992', '408194728', '408415149', '409845781', '410360259', '413705161', '415018464', '418108171', '418147906', '419829403', '419893102', '420414971', '420732992', '421057183', '422508051', '422929646', '423856459', '423979806', '424275691', '424630986', '428184493', '429815202', '429955297', '429986532', '431627322', '431864941', '433019858', '433999356', '438052219', '44053200000', '440668143', '442688719', '443473988', '443511241', '444525828', '451329908', '451338545', '451464614', '451912778', '452374937', '479361412', '479768590', '480392919', '48326316600025', '487567836', '490711629', '491125092', '491363768', '492189196', '492399506', '492846506', '493075931', '493244446', '493420939', '499596070', '500109616', '500180542', '500884432', '500943717', '502488588', '502848708', '503930687', '505399949', '507771483', '508296506', '508806676', '512552373', '521628255', '528244387', '529145260', '529170128', '529342503', '531488658', '538451196', '602044638', '682003991', '713650075', '722023728', '788621563', '802240382', '803956119', '808642557', '812618536', '814165239', '831417134', '844236448', '878131655', '879428050', '880719448', '88165063400010', '998823504') or `STRUCTURE_ETT`.`ORG_SIREN_ETT` is null) and (DATE(DATE_ARCHI.DATE_ID) BETWEEN '2009-01-01' AND DATE_ADD(DATE(NOW()), INTERVAL 2 YEAR) OR DATE(DATE_ARCHI.DATE_ID)='1900-01-01') and `FAIT_ASSIGNMENTS`.`DATE_ARCHIVAGE` = `DATE_ARCHI`.`DATE_NUM` and `DATE_ARCHI`.`ANNEE` in ('2021', '2022') and `DATE_ARCHI`.`TRIMESTRE` in ('2021-T2', '2021-T3', '2021-T4', '2022-T1', '2022-T2') and `DATE_ARCHI`.`MOIS` in ('2021-05', '2021-06', '2021-07', '2021-08', '2021-09', '2021-10', '2021-11', '2021-12', '2022-01', '2022-02', '2022-03', '2022-04') and `FAIT_ASSIGNMENTS`.`ASS_ID` = `TYPE_CTR`.`ASS_ID` group by `STRUCTURE_EU`.`N1_ORG_ID`, `STRUCTURE_EU`.`ORG_SIREN_ENSEIGNE`, `STRUCTURE_EU`.`N2_ORG_ID`, `STRUCTURE_ETT`.`N1_ORG_ID`, `STRUCTURE_ETT`.`ORG_SIREN_ETT`, `DATE_ARCHI`.`ANNEE`, `DATE_ARCHI`.`TRIMESTRE`, `DATE_ARCHI`.`MOIS`, `TYPE_CTR`.`REF_LIBELLE`