SELECT b.calldate,b.src, CONCAT(b.prefix, b.number) as dst , b.billsec, b.billmin, z.price, ROUND(CEIL(b.billmin)*z.price, 2) as bill, t.numbertype, i.ispname,s.subregionname,r.regionname
FROM (SELECT h.calldate ,h.src,  SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 ) AS number, h.billsec , ROUND(h.billsec/60, 2) as billmin
FROM  asteriskcdrdb.cdr h WHERE  calldate BETWEEN  '2015-05-01 00:00:00' AND  '2015-05-31 23:59:59' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED') as b 
inner join astcdr.codes  c on b.prefix=c.abcdef
inner join astcdr.zoneinfo zi on zi.uid=c.zoneinfouid
inner join astcdr.zone z on z.uid=zi.zoneuid
inner join astcdr.isp i on i.uid=zi.ispuid
inner join astcdr.numtype t on t.uid=c.type
inner join astcdr.region r on r.uid=zi.regionuid
inner join astcdr.subregion s on s.uid=zi.subregionuid
WHERE  b.number between c.start and c.end;

SELECT b.calldate,b.src, CONCAT(b.prefix, b.number) as dst , b.duration, b.billsec, b.billmin, z.price, ROUND(CEIL(b.billmin)*z.price, 2) as bill, t.numbertype, i.ispname,s.subregionname,r.regionname
FROM (SELECT h.calldate ,h.src,  SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 ) AS number,h.duration, h.billsec , 
ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2) as billmin
FROM  asteriskcdrdb.cdr h WHERE  calldate BETWEEN  '2015-05-01 00:00:00' AND  '2015-05-31 23:59:59' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED') as b 
inner join astcdr.codes  c on b.prefix=c.abcdef
inner join astcdr.zoneinfo zi on zi.uid=c.zoneinfouid
inner join astcdr.zone z on z.uid=zi.zoneuid
inner join astcdr.isp i on i.uid=zi.ispuid
inner join astcdr.numtype t on t.uid=c.type
inner join astcdr.region r on r.uid=zi.regionuid
inner join astcdr.subregion s on s.uid=zi.subregionuid
WHERE  b.number between c.start and c.end;