SELECT startofchargingdate,'siemens' as proveedor, servedmsisdn,MIN(servedimsi) as servedimsi,min(inflag) as inflag, SUM(CASE WHEN calltransactiontype='01' AND recordtype IN ('00','01') THEN 1 ELSE 0 END) as qt_moc, SUM(CASE WHEN calltransactiontype='01' THEN callduration ELSE 0 END)/60 AS min_moc, SUM(CASE WHEN calltransactiontype='01' AND recordtype IN ('00','01') AND (dialledotherpartyb regexp '^30[01234]' OR dialled otherpartyb regexp '^5730[01234]') THEN 1 ELSE 0 END) AS qt_moc_ola, SUM(CASE WHEN calltransactiontype='01' AND (dialledotherpartyb regexp '^30[01234]' OR dialledotherpartyb regexp '^5730[01234 ]') THEN callduration ELSE 0 END)/60 AS min_moc_ola, SUM(CASE WHEN calltransactiontype='01' AND recordtype IN ('00','01') AND (dialled_b_3 BETWEEN '310' AND '314' OR dialled_b_5 BETWEEN '57310' AND '57314') THEN 1 ELSE 0 END) as qt_moc_comcel, SUM(CASE WHEN calltransactiontype='01' AND (dialled_b_3 BETWEEN '310' AND '314' OR dialled_b_5 BETWEEN '57310' AND '57314') THEN callduration ELSE 0 END)/60 AS min_moc_comcel, SUM(CASE WHEN calltransactiontype='01' AND recordtype IN ('00','01') AND (dialled_b_3 BETWEEN '315' AND '319' OR dialled_b_5 BETWEEN '57315' AND '57319') THEN 1 ELSE 0 END) AS qt_moc_movistar, SUM(CASE WHEN calltransactiontype='01' AND (dialled_b_3 BETWEEN '315' AND '319' OR dialled_b_5 BETWEEN '57315' AND '57319') THEN callduration ELSE 0 END)/60 AS min_moc_movistar, SUM(CASE WHEN calltransactiontype='01' AND recordtype IN ('00','01') AND (dialledOtherPartyB LIKE '03________' OR dialledOth erPartyB LIKE '57________' OR dialledOtherPartyB LIKE '0057________' OR dialledOtherPartyB LIKE '01800_______' OR dialledOth erPartyB LIKE '0190________' OR dialledOtherPartyB LIKE 'a___' OR dialledOtherPartyB LIKE '1__') THEN 1 ELSE 0 END) AS qt_m oc_fijoNal, SUM(CASE WHEN calltransactiontype='01' AND (dialledOtherPartyB LIKE '03________' OR dialledOtherPartyB LIKE '57________' OR dialledOtherPartyB LIKE '0057________' OR dialledOtherPartyB LIKE '01800_______' OR dialledOtherPartyB LIKE '0190________' O R dialledOtherPartyB LIKE 'a___' OR dialledOtherPartyB LIKE '1__') THEN callduration ELSE 0 END)/60 AS min_moc_fijoNal, SUM(CASE WHEN calltransactiontype='01' AND recordtype IN ('00','01') AND dialled_b_2 = '00' THEN 1 ELSE 0 END) AS qt_moc_ldi, SUM(CASE WHEN calltransactiontype='01' AND dialled_b_2 = '00' THEN callduration ELSE 0 END)/60 AS min_moc_ldi, SUM(CASE WHEN calltransactiontype='01' AND recordtype IN ('00','01') AND NOT (dialledotherpartyb regexp '^30[01234]' OR dial ledotherpartyb regexp '^5730[01234]') AND NOT (dialled_b_3 BETWEEN '310' AND '314' OR dialled_b_5 BETWEEN '57310' AND '57314 ') AND NOT (dialled_b_3 BETWEEN '315' AND '319' OR dialled_b_5 BETWEEN '57315' AND '57319') AND NOT (dialledOtherPartyB LIKE '03________' OR dialledOtherPartyB LIKE '57________' OR dialledOtherPartyB LIKE '0057________' OR dialledOtherPartyB LIKE ' 01800_______' OR dialledOtherPartyB LIKE '0190________' OR dialledOtherPartyB LIKE 'a___' OR dialledOtherPartyB LIKE '1__') AND NOT (dialled_b_2 = '00') THEN 1 ELSE 0 END) as qt_moc_otros, SUM(CASE WHEN calltransactiontype='01' AND NOT (dialledotherpartyb regexp '^30[01234]' OR dialledotherpartyb regexp '^5730[0 1234]') AND NOT (dialled_b_3 BETWEEN '310' AND '314' OR dialled_b_5 BETWEEN '57310' AND '57314') AND NOT (dialled_b_3 BETWEE N '315' AND '319' OR dialled_b_5 BETWEEN '57315' AND '57319') AND NOT (dialledOtherPartyB LIKE '03________' OR dialledOtherP artyB LIKE '57________' OR dialledOtherPartyB LIKE '0057________' OR dialledOtherPartyB LIKE '01800_______' OR dialledOtherPartyB LIKE '0190________' OR dialledOtherPartyB LIKE 'a___' OR dialledOtherPartyB LIKE '1__') AND NOT (dialled_b_2 = '00') T HEN callduration ELSE 0 END)/60 as min_moc_otros, SUM(CASE WHEN calltransactiontype='02' AND recordtype IN ('00','01') THEN 1 ELSE 0 END) AS qt_mtc, SUM(CASE WHEN calltransactiontype='02' THEN callduration ELSE 0 END)/60 AS min_mtc, SUM(CASE WHEN calltransactiontype='02' AND recordtype IN ('00','01') AND (dialledotherpartyb regexp '^30[01234]' OR dialledo therpartyb regexp '^5730[01234]') THEN 1 ELSE 0 END) AS qt_mtc_ola, SUM(CASE WHEN calltransactiontype='02' AND (dialledotherpartyb regexp '^30[01234]' OR dialledotherpartyb regexp '^5730[01234 ]') THEN callduration ELSE 0 END)/60 AS min_mtc_ola, SUM(CASE WHEN calltransactiontype='02' AND recordtype IN ('00','01') AND (dialled_b_3 between '310' and '314' OR dialled_b_5 between '57310' and '57314') THEN 1 ELSE 0 END) AS qt_mtc_comcel, SUM(CASE WHEN calltransactiontype='02' AND (dialled_b_3 between '310' and '314' OR dialled_b_5 between '57310' and '57314') THEN callduration ELSE 0 END)/60 AS min_mtc_comcel, SUM(CASE WHEN calltransactiontype='02' AND recordtype IN ('00','01') AND (dialled_b_3 between '315' and '319' OR dialled_b_5 between '57315' and '57319') THEN 1 ELSE 0 END) AS qt_mtc_movistar, SUM(CASE WHEN calltransactiontype='02' AND (dialled_b_3 between '315' and '319' OR dialled_b_5 between '57315' and '57319') THEN callduration ELSE 0 END)/60 as min_mtc_movistar, SUM(CASE WHEN calltransactiontype='02' AND recordtype IN ('00','01') AND NOT (dialledotherpartyb regexp '^30[01234]' OR dial ledotherpartyb regexp '^5730[01234]') AND NOT (dialled_b_3 between '310' and '314' OR dialled_b_5 between '57310' and '57 314') AND NOT (dialled_b_3 between '315' and '319' OR dialled_b_5 between '57315' and '57319') AND dialledotherpartyb NOT LI KE '190%' AND LENGTH(dialledotherpartyb) < 8 THEN 1 ELSE 0 END) AS qt_mtc_fijoNal, SUM(CASE WHEN calltransactiontype='02' AND NOT (dialledotherpartyb regexp '^30[01234]' OR dialledotherpartyb regexp '^5730[0 1234]') AND NOT (dialled_b_3 between '310' and '314' OR dialled_b_5 between '57310' and '57314') AND NOT (dialled_b_3 bet ween '315' and '319' OR dialled_b_5 between '57315' and '57319') AND dialledotherpartyb not like '190%' AND LENGTH(dialledot herpartyb)<8 THEN callduration ELSE 0 END)/60 AS min_mtc_fijoNal, SUM(CASE WHEN calltransactiontype='02' AND recordtype IN ('00','01') AND NOT (dialledotherpartyb regexp '^30[01234]' OR dial ledotherpartyb regexp '^5730[01234]') AND NOT (dialled_b_3 between '310' and '314' OR dialled_b_5 between '57310' and '57 314') AND NOT (dialled_b_3 between '315' and '319' OR dialled_b_5 between '57315' and '57319') AND NOT dialledotherpartyb no t like '190%' AND NOT LENGTH(dialledotherpartyb)<8 THEN 1 ELSE 0 END) AS qt_mtc_otros, SUM(CASE WHEN calltransactiontype='02' AND NOT (dialledotherpartyb regexp '^30[01234]' OR dialledotherpartyb regexp '^5730[0 1234]') AND NOT (dialled_b_3 between '310' and '314' OR dialled_b_5 between '57310' and '57314') AND NOT (dialled_b_3 bet ween '315' and '319' OR dialled_b_5 between '57315' and '57319') AND NOT dialledotherpartyb not like '190%' AND NOT LENGTH(d ialledotherpartyb)<8 THEN callduration ELSE 0 END)/60 AS min_mtc_otros, SUM(CASE WHEN calltransactiontype='1d' AND recordtype IN ('00','01') THEN 1 ELSE 0 END) AS qt_cfw, SUM(CASE WHEN calltransactiontype='1d' THEN callduration ELSE 0 END)/60 AS min_cfw, SUM(CASE WHEN calltransactiontype='1d' AND recordtype IN ('00','01') AND dialledotherpartyb NOT IN ('573003690005','57300669 0005','573008690005') THEN 1 ELSE 0 END) AS qt_cfw_novms,SUM(CASE WHEN calltransactiontype='1d' AND dialledotherpartyb NOT IN ('573003690005','573006690005','573008690005') THEN cal lduration ELSE 0 END)/60 AS min_cfw_novms, SUM(CASE WHEN calltransactiontype='1d' AND recordtype IN ('00','01') AND dialledotherpartyb NOT IN ('573003690005','57300669 0005','573008690005') AND (dialledotherpartyb regexp '^30[01234]' OR dialledotherpartyb regexp '^5730[01234]') THEN 1 ELSE 0 END) as qt_cfw_ola, SUM(CASE WHEN calltransactiontype='1d' AND dialledotherpartyb NOT IN ('573003690005','573006690005','573008690005') AND (dia lledotherpartyb regexp '^30[01234]' OR dialledotherpartyb regexp '^5730[01234]') THEN callduration ELSE 0 END)/60 AS min_cfw _ola, SUM(CASE WHEN calltransactiontype='1d' AND recordtype IN ('00','01') AND (dialled_b_3 BETWEEN '310' AND '314' OR dialled_b_5 BETWEEN '57310' AND '57314') THEN 1 ELSE 0 END) AS qt_cfw_comcel, SUM(CASE WHEN calltransactiontype='1d' AND (dialled_b_3 BETWEEN '310' AND '314' OR dialled_b_5 BETWEEN '57310' AND '57314') THEN callduration ELSE 0 END)/60 AS min_cfw_comcel, SUM(CASE WHEN calltransactiontype='1d' AND recordtype IN ('00','01') AND (dialled_b_3 between '315' and '319' OR dialled_b_5 between '57315' and '57319') THEN 1 ELSE 0 END) AS qt_cfw_movistar, SUM(CASE WHEN calltransactiontype='1d' AND (dialled_b_3 between '315' and '319' OR dialled_b_5 between '57315' and '57319') THEN callduration ELSE 0 END)/60 AS min_cfw_movistar, SUM(CASE WHEN calltransactiontype='1d' AND recordtype IN ('00','01') AND (dialledOtherPartyB LIKE '03________' OR dialledOth erPartyB LIKE '57________' OR dialledOtherPartyB LIKE '0057________' OR dialledOtherPartyB LIKE '01800_______' OR dialledOth erPartyB LIKE '0190________' OR dialledOtherPartyB LIKE 'a___' OR dialledOtherPartyB LIKE '1__') THEN 1 ELSE 0 END) AS qt_cf w_fijoNal, SUM(CASE WHEN calltransactiontype='1d' AND (dialledOtherPartyB LIKE '03________' OR dialledOtherPartyB LIKE '57________' OR dialledOtherPartyB LIKE '0057________' OR dialledOtherPartyB LIKE '01800_______' OR dialledOtherPartyB LIKE '0190________' O R dialledOtherPartyB LIKE 'a___' OR dialledOtherPartyB LIKE '1__') THEN callduration ELSE 0 END)/60 AS min_cfw_fijoNal, SUM(CASE WHEN calltransactiontype='1d' AND recordtype IN ('00','01') AND NOT (dialledotherpartyb regexp '^30[01234]' OR dial ledotherpartyb regexp '^5730[01234]') AND NOT (dialled_b_3 BETWEEN '310' AND '314' OR dialled_b_5 BETWEEN '57310' AND '57314 ') AND NOT (dialled_b_3 BETWEEN '315' AND '319' OR dialled_b_5 BETWEEN '57315' AND '57319') AND NOT (dialledOtherPartyB LIKE '03________' OR dialledOtherPartyB LIKE '57________' OR dialledOtherPartyB LIKE '0057________' OR dialledOtherPartyB LIKE ' 01800_______' OR dialledOtherPartyB LIKE '0190________' OR dialledOtherPartyB LIKE 'a___' OR dialledOtherPartyB LIKE '1__') THEN 1 ELSE 0 END) as qt_cfw_otros, SUM(CASE WHEN calltransactiontype='1d' AND NOT (dialledotherpartyb regexp '^30[01234]' OR dialledotherpartyb regexp '^5730[0 1234]') AND NOT (dialled_b_3 BETWEEN '310' AND '314' OR dialled_b_5 BETWEEN '57310' AND '57314') AND NOT (dialled_b_3 BETWEE N '315' AND '319' OR dialled_b_5 BETWEEN '57315' AND '57319') AND NOT (dialledOtherPartyB LIKE '03________' OR dialledOtherP artyB LIKE '57________' OR dialledOtherPartyB LIKE '0057________' OR dialledOtherPartyB LIKE '01800_______' OR dialledOtherP artyB LIKE '0190________' OR dialledOtherPartyB LIKE 'a___' OR dialledOtherPartyB LIKE '1__') THEN callduration ELSE 0 END)/ 60 as min_cfw_otros, SUM(CASE WHEN calltransactiontype='1f' THEN 1 ELSE 0 END) AS qt_mocSMS,SUM(CASE WHEN calltransactiontype='1f' AND (otherpartySms regexp '^30[01234]' OR otherpartySms regexp '^5730[01234]') THEN 1 ELSE 0 END) AS qt_mocSMS_ola, SUM(CASE WHEN calltransactiontype='1f' AND (left(otherpartySms,3) between '310' and '314' OR left(otherpartySms,5) between ' 57310' and '57314') THEN 1 ELSE 0 END) AS qt_mocSMS_comcel, SUM(CASE WHEN calltransactiontype='1f' AND (left(otherpartySms,3) between '315' and '319' OR left(otherpartySms,5) between ' 57315' and '57319') THEN 1 ELSE 0 END) AS qt_mocSMS_movistar, SUM(CASE WHEN calltransactiontype='1f' AND NOT (otherpartySms regexp '^30[01234]' OR otherpartySms regexp '^5730[01234]') AN D NOT (left(otherpartySms,3) between '310' and '314' OR left(otherpartySms,5) between '57300' and '57314') AND NOT (left(oth erpartySms,3) between '315' and '319' OR left(otherpartySms,5) between '57315' and '57319') THEN 1 ELSE 0 END) AS qt_mocSMS_ otros, SUM(CASE WHEN calltransactiontype='1e' THEN 1 ELSE 0 END) AS qt_mtcSMS, SUM(CASE WHEN calltransactiontype='1e' AND (otherpartySms regexp '^30[01234]' OR otherpartySms regexp '^5730[01234]') THEN 1 ELSE 0 END) AS qt_mtcSMS_ola, SUM(CASE WHEN calltransactiontype='1e' AND (left(otherpartySms,3) between '310' and '314' OR left(otherpartySms,5) between ' 57310' and '57314') THEN 1 ELSE 0 END) AS qt_mtcSMS_comcel, SUM(CASE WHEN calltransactiontype='1e' AND (left(otherpartySms,3) between '315' and '319' OR left(otherpartySms,5) between '57315' and '57319') THEN 1 ELSE 0 END) AS qt_mtcSMS_movistar, SUM(CASE WHEN calltransactiontype='1e' AND NOT (otherpartySms regexp '^30[01234]' OR otherpartySms regexp '^5730[01234]') AN D NOT (left(otherpartySms,3) between '310' and '314' OR left(otherpartySms,5) between '57310' and '57314') AND NOT (left(otherpartySms,3) between '315' and '319' OR left(otherpartySms,5) between '57315' and '57319') THEN 1 ELSE 0 END) AS qt_mtcSMS_otros FROM cdrs.cdr_20070501 WHERE calltransactiontype IN ('01','02','1d','1e','1f') AND IF(calltransactiontype IN ('01','02','1d') AND (callduration IS NULL or callduration = 0),0,1) AND IF(calltransactiontype IN ('01','02','1d') AND tgrpnameog LIKE '_IP%',0,1) AND IF(calltra nsactiontype IN ('01','02','1d') AND tgrpnameog LIKE '_BNC%',0,1) AND IF(length(dialledotherpartyb)=3 AND dialledotherpartyb NOT IN ('113','106','117','122','133','137','142','143','152','153','157','166','167','195'),0,1) AND IF(calltransactiontyp e IN ('01','02','1d') AND (INSTR(dialledotherpartyb,'a')!=0 OR INSTR(dialledotherpartyb,'b')!=0) AND dialledotherpartyb NOT IN('b322','b411','b555','b609','b622','b888','b454','b468','b444','b284','b593','b462','b545','b789'),0,1) AND IF(calltransactiontype IN ('01','02') AND dialledotherpartyb IN ('573003690005','573006690005','573008690005','300589','f'),0,1) AND IF (calltransactiontype IN ('1e','1f') AND serviceCentreAddress IN ('573006690052','573006690053'),0,1) AND IF(calltransactiont ype IN ('1e','1f') AND otherpartysms IN ('573003690012','573003690025','573003690019','3003690020','3003690052','3003000000' ,'f46601','f','f466014947b9d26781','2000681460979007','9017','7000','190','7103','8202','767','526','9038','5562','126','556 4','7001','8502','8503','192','193','194','9041','195','7003','7004','7005','182','183','7101','5559','5560','5561','5563',' 8105','9037','7701','8201','9042','9043','77333','9045','180','181','9048','9047','9049','184','8102','8203','8204','8101',' 8205','8501','191','8208','8106','9046','7002','9040','8103','8104','7104','7105','8207','8206','7206','7103','9044'),0,1) AND IF(calltransactiontype ='1e' AND length(otherpartysms)<=6,0,1) GROUP BY 1,2,3 EXPLAIN id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE cdr_20070412 ALL ix_nombre_tabla_ctt NULL NULL NULL 17279250 Using where; Using temporary; Using filesort