+----+-------------+--------------+------+---------------------------------------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+---------------------------------------------+------+---------+------+----------+----------------------------------------------+
| 1 | SIMPLE | cdr_20070501 | ALL | ix_nombre_tabla_ctt,ix_nombre_tabla_dialled | NULL | NULL | NULL | 12356709 | Using where; Using temporary; Using filesort |
+----+-------------+--------------+------+---------------------------------------------+------+---------+------+----------+----------------------------------------------+
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 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_moc_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________' OR 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 dialledotherpartyb 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[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 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 dialledotherpartyb 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 dialledotherpartyb 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 dialledotherpartyb NOT LIKE '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[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 dialledotherpartyb not like '190%' AND LENGTH(dialledotherpartyb)<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 dialledotherpartyb 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 not 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[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 not like '190%' AND NOT LENGTH(dialledotherpartyb)<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','573006690005','573008690005') THEN 1 ELSE 0 END) AS qt_cfw_novms,
SUM(CASE WHEN calltransactiontype='1d' AND dialledotherpartyb NOT IN ('573003690005','573006690005','573008690005') THEN callduration ELSE 0 END)/60 AS min_cfw_novms,
SUM(CASE WHEN calltransactiontype='1d' AND recordtype IN ('00','01') AND dialledotherpartyb NOT IN ('573003690005','573006690005','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 (dialledotherpartyb 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 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_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________' OR 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 dialledotherpartyb 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[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 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]') AND NOT (left(otherpartySms,3) between '310' and '314' OR left(otherpartySms,5) between '57300' 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_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]') AND 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 NOT (
calltransactiontype IN ('01','02','1d')
AND (callduration IS NULL or callduration = 0)
)
AND NOT (
calltransactiontype IN ('01','02','1d')
AND tgrpnameog LIKE '_IP%',
)
AND NOT (
calltransactiontype IN ('01','02','1d')
AND tgrpnameog LIKE '_BNC%',
)
AND NOT (
length(dialledotherpartyb)=3
AND dialledotherpartyb NOT IN ('113','106','117','122','133','137','142','143','152','153','157','166','167','195'),
)
AND NOT (
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'),
)
AND NOT (
calltransactiontype IN ('01','02')
AND dialledotherpartyb IN ('573003690005','573006690005','573008690005','300589','f'),
)
AND NOT (
calltransactiontype IN ('1e','1f')
AND serviceCentreAddress IN ('573006690052','573006690053'),
)
AND NOT (
calltransactiontype 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','5564','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'),
)
AND NOT (
calltransactiontype ='1e'
AND length(otherpartysms)<=6,
)
GROUP BY 1,2,3