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