SELECT SQL_CALC_FOUND_ROWS premiereTrans,derniereTrans,CA,devise, contrat,contratID, libelleContrat, application,site,entrepriseID,groupe,numTrans FROM ((SELECT A.premiereTrans, A.derniereTrans, A.CA,A.devise, CONCAT(sa.pxsanumcontrat_tc,'-',sa.pxsanumlogique_tc) as contrat, A.contratID, sa.pxsalibelle_tc as libelleContrat, \n\t\t\tap.apnom as application, e.customerName AS site, e.entrepriseID as entrepriseID, e2.customerName AS groupe, A.numTrans\n\t\t\tFROM (\n\t\t\tSELECT SQL_NO_CACHE MIN(agr.pxtlagrtransaction_d) AS premiereTrans,\n\t\t\tMAX(agr.pxtlagrtransaction_d) AS derniereTrans,\n\t\t\t(case MIN(agr.pxtlagrtransaction_d) is null when true then 0 else SUM(agr.pxtlagrcumul_l)/POW(10,agr.pxtlagrnbrdecimales_i) END) AS CA,\n\t\t\tagr.pxtlagrmonnaie_tc AS devise,\n\t\t\tagr.pxtlagridsystacc_l as contratID, \n\t\t\tsum(pxtlagrnumtranscredit_l+pxtlagrnumtransdebit_l+pxtlagrnumtransannulation_l+pxtlagrnumtransannulationcredit_l) as numTrans, \n\t\t\tpxtlagridmagasin_l,\n\t\t\tpxtlagridsystacc_l,\n\t\t\tpxtlagridsousenseigne_l,\n\t\t\tpxtlagridenseigne_l,\n\t\t\tpxtlagrtypeappli_l \n\t\t\tFROM proxyBOC.fpxtelecoll_agr  agr WHERE  agr.pxtlagrtransaction_d BETWEEN '2011-01-27' AND '2011-02-11' AND agr.pxtlagridenseigne_l='7' AND agr.pxtlagrmasquer_i != 1 GROUP BY contratID,devise\n\n\t\t\t)A \n\t\t\tLEFT JOIN tpewebv2.fpxsystacc sa on (A.pxtlagridsystacc_l =sa.pxsarowid_l)\n\t\t\tLEFT JOIN tpewebv2.fappli ap on (A.pxtlagrtypeappli_l=ap.apid)\n\t\t\tLEFT JOIN proxyBOC.Magasin m on (A.pxtlagridmagasin_l = m.entrepriseID )\n\t\t\tLEFT JOIN proxyBOC.SousEnseigne se on (A.pxtlagridsousenseigne_l=se.entrepriseID) \n\t\t\tLEFT JOIN proxyBOC.Entreprise e on (A.pxtlagridmagasin_l=e.entrepriseID)\n\t\t\tLEFT JOIN proxyBOC.Entreprise e2 on (A.pxtlagridsousenseigne_l=e2.entrepriseID) WHERE ap.apnom IN ("AMEX","CB","CETNCB","METIER") ) UNION ALL(\tSELECT DATE_FORMAT(MIN(mh.pxmhmodif_ts),'%Y-%m-%d') as premiereTrans,DATE_FORMAT( MAX(mh.pxmhmodif_ts),'%Y-%m-%d') as derniereTrans,0 as CA,'N/A' as devise, CONCAT(sa.pxsanumcontrat_tc,'-',sa.pxsanumlogique_tc) as contrat, pxsarowid_l as contratID, sa.pxsalibelle_tc as libelleContrat,  ap.apnom as application, e.customerName AS site,e.entrepriseID as entrepriseID, e2.customerName AS groupe, COUNT(mh.pxmhsession_l) as numTrans from tpewebv2.fpxmetierhistoWS mh JOIN tpewebv2.fpxsystacc sa on (mh.pxmhidsystacc_l=sa.pxsarowid_l) join proxyBOC.fpxmagasin_systacc msa on sa.pxsarowid_l=msa.pxmsidsystacc_l\n LEFT join proxyBOC.SousSite s on (msa.pxmsidMagasin_l=s.entrepriseID)\n join proxyBOC.Magasin m on (msa.pxmsidMagasin_l = m.entrepriseID )\n join proxyBOC.SousEnseigne se on (se.entrepriseID=m.idSousEnseigne and se.idEnseigne='7') join proxyBOC.Entreprise e on (e.entrepriseID=m.EntrepriseID) left join proxyBOC.Entreprise e2 on (e2.entrepriseID=m.idSousEnseigne)  JOIN tpewebv2.fappli ap on (sa.pxsatypeappli_l=ap.apid)WHERE mh.pxmhmodif_ts BETWEEN '2011-01-27 00:00:00' AND '2011-02-11 23:59:59'  AND msa.masquer != 1 GROUP BY contratID) UNION DISTINCT(\tSELECT '0000-00-00' as premiereTrans, '0000-00-00' as derniereTrans,0 as CA,IF((ap.apnom='METIER' OR ap.apnom='FNCI'),'N/A','') as devise, CONCAT(sa.pxsanumcontrat_tc,'-',sa.pxsanumlogique_tc) as contrat, pxsarowid_l as contratID, sa.pxsalibelle_tc as libelleContrat,  ap.apnom as application, e.customerName AS site,e.entrepriseID as entrepriseID, e2.customerName AS groupe,0 as numTrans from tpewebv2.fpxsystacc sa join proxyBOC.fpxmagasin_systacc msa on sa.pxsarowid_l=msa.pxmsidsystacc_l\n LEFT join proxyBOC.SousSite s on (msa.pxmsidMagasin_l=s.entrepriseID)\n join proxyBOC.Magasin m on (msa.pxmsidMagasin_l = m.entrepriseID )\n join proxyBOC.SousEnseigne se on (se.entrepriseID=m.idSousEnseigne and se.idEnseigne='7') join proxyBOC.Entreprise e on (e.entrepriseID=m.EntrepriseID) left join proxyBOC.Entreprise e2 on (e2.entrepriseID=m.idSousEnseigne)  JOIN tpewebv2.fappli ap on (sa.pxsatypeappli_l=ap.apid) WHERE ap.apnom IN ("AMEX","CB","CETNCB","METIER")  AND msa.masquer != 1 GROUP BY contratID,devise)) AS T1 GROUP BY T1.contratID,IF(T1.devise='','EUR',T1.devise)