
02.16 17:48:39.771 Started
  
create temporary table tmp_r select  sd.id_stat_zone as id_stat_zone, sd.zone as zone, sd.dir as dir, count(*) as total, sum(if(is_repeat=0, 0, 1)) as repeat, sum(if(t.netindex='205997', 1, 0)) as avtokss, sum(if(t.is_serv>=3, 1, 0)) as sending, sum(if(is_serv>0 and is_serv<3, 1, 0)) as serv, sum(if(is_perevod=0, 0, 1)) as perev, sum(if(is_peregovor=0, 0, 1)) as peregov, sum(if(t.category like '%' or z.country>1, 1, 0)) as inter from tlg as t inner join stat_dir as sd on sd.zone=t.r_zone and sd.dir=t.r_direct left join zone as z on z.zone=left(t.netindex, 3) where r_date>='2013-01-01' and r_date<='2013-01-31' group by sd.id_stat_zone, zone, dir
 ,  
select  count(*) as total, count(*)-sum(if(summa>0, 1, 0)) as serv, sum(is_perevod) as perev, sum(is_peregovor) as peregov, sum(if(t.category like '%' or z.country>1, 1, 0)) as inter from tlg as t left join zone as z on z.zone=left(t.netindex, 3)where  r_date>='2013-01-01' and r_date<='2013-01-31' and r_zone='205' and r_direct like '9__' and rao_index is not null
Update
update tmp_r set total=total+0, serv=serv+, perev=perev+, peregov=peregov+, inter=inter+ where zone='205' and dir='-02'
02.16 17:49:38.836 Stopped

02.16 17:54:08.775 Started
  
create temporary table tmp_r select  sd.id_stat_zone as id_stat_zone, sd.zone as zone, sd.dir as dir, count(*) as total, sum(if(is_repeat=0, 0, 1)) as repeat, sum(if(t.netindex='205997', 1, 0)) as avtokss, sum(if(t.is_serv>=3, 1, 0)) as sending, sum(if(is_serv>0 and is_serv<3, 1, 0)) as serv, sum(if(is_perevod=0, 0, 1)) as perev, sum(if(is_peregovor=0, 0, 1)) as peregov, sum(if(t.category like '%' or z.country>1, 1, 0)) as inter from tlg as t inner join stat_dir as sd on sd.zone=t.r_zone and sd.dir=t.r_direct left join zone as z on z.zone=left(t.netindex, 3) where r_date>='2012-12-01' and r_date<='2013-01-31' group by sd.id_stat_zone, zone, dir
 ,  
select  count(*) as total, count(*)-sum(if(summa>0, 1, 0)) as serv, sum(is_perevod) as perev, sum(is_peregovor) as peregov, sum(if(t.category like '%' or z.country>1, 1, 0)) as inter from tlg as t left join zone as z on z.zone=left(t.netindex, 3)where  r_date>='2012-12-01' and r_date<='2013-01-31' and r_zone='205' and r_direct like '9__' and rao_index is not null
Update
update tmp_r set total=total+0, serv=serv+, perev=perev+, peregov=peregov+, inter=inter+ where zone='205' and dir='-02'
02.16 17:59:38.178 Started
  
create temporary table tmp_r select  sd.id_stat_zone as id_stat_zone, sd.zone as zone, sd.dir as dir, count(*) as total, sum(if(is_repeat=0, 0, 1)) as repeat, sum(if(t.netindex='205997', 1, 0)) as avtokss, sum(if(t.is_serv>=3, 1, 0)) as sending, sum(if(is_serv>0 and is_serv<3, 1, 0)) as serv, sum(if(is_perevod=0, 0, 1)) as perev, sum(if(is_peregovor=0, 0, 1)) as peregov, sum(if(t.category like '%' or z.country>1, 1, 0)) as inter from tlg as t inner join stat_dir as sd on sd.zone=t.r_zone and sd.dir=t.r_direct left join zone as z on z.zone=left(t.netindex, 3) where r_date>='2012-12-01' and r_date<='2013-01-31' group by sd.id_stat_zone, zone, dir
 ,  
select  count(*) as total, count(*)-sum(if(summa>0, 1, 0)) as serv, sum(is_perevod) as perev, sum(is_peregovor) as peregov, sum(if(t.category like '%' or z.country>1, 1, 0)) as inter from tlg as t left join zone as z on z.zone=left(t.netindex, 3)where  r_date>='2012-12-01' and r_date<='2013-01-31' and r_zone='205' and r_direct like '9__' and rao_index is not null
Update
update tmp_r set total=total+0, serv=serv+, perev=perev+, peregov=peregov+, inter=inter+ where zone='205' and dir='-02'
02.16 18:03:13.528 Stopped

06.08 16:57:00.796 Started
  
create temporary table tmp_r select  sd.id_stat_zone as id_stat_zone, sd.zone as zone, sd.dir as dir, count(*) as total, sum(if(is_repeat=0, 0, 1)) as repeat, sum(if(t.netindex='244997', 1, 0)) as avtokss, sum(if(t.is_serv>=3, 1, 0)) as sending, sum(if(is_serv>0 and is_serv<3, 1, 0)) as serv, sum(if(is_perevod=0, 0, 1)) as perev, sum(if(is_peregovor=0, 0, 1)) as peregov, sum(if(t.category like '%' or z.country>1, 1, 0)) as inter from tlg as t inner join stat_dir as sd on sd.zone=t.r_zone and sd.dir=t.r_direct left join zone as z on z.zone=left(t.netindex, 3) where r_date>='2015-05-01' and r_date<='2015-05-31' group by sd.id_stat_zone, zone, dir
 ,  
select  count(*) as total, count(*)-sum(if(summa>0, 1, 0)) as serv, sum(is_perevod) as perev, sum(is_peregovor) as peregov, sum(if(t.category like '%' or z.country>1, 1, 0)) as inter from tlg as t left join zone as z on z.zone=left(t.netindex, 3)where  r_date>='2015-05-01' and r_date<='2015-05-31' and r_zone='244' and r_direct like '9__' and rao_index is not null
Update
update tmp_r set total=total+0, serv=serv+, perev=perev+, peregov=peregov+, inter=inter+ where zone='244' and dir='-02'
  
create temporary table tmp_s select  sd.id_stat_zone as id_stat_zone, sd.zone as zone, sd.dir as dir, count(*) as total, sum(if(t.category like '%' or z.country>1, 1, 0)) as inter from tlg as t inner join stat_dir as sd on sd.zone=t.s_zone and sd.dir=t.s_direct left join zone as z on z.zone=t.source_cks where s_date>='2015-05-01' and s_date<='2015-05-31' group by sd.id_stat_zone, zone, dir
  
select
  z.name as z_name
, concat(d.zone, d.dir, '/00 ', d.name) as d_dir
, if(r.total is null, 0, r.total) as r_total
, if(r.avtokss is null, 0, r.avtokss) as avtokss
, if(r.sending is null, 0, r.sending) as sending
, if(r.repeat is null, 0, r.repeat) as repeat
, if(r.serv is null, 0, r.serv) as r_serv
, if(r.total is null, 0, r.total-r.serv-r.avtokss-r.sending-.r.repeat) as r_platn
, if(r.perev is null, 0, r.perev) as r_perev
, if(r.peregov is null, 0, r.peregov) as r_peregov
, if(r.inter is null, 0, r.inter) as r_inter
, s.total as s_total
, if(s.total is null, 0, s.total-r.avtokss-r.sending) as s_totala
, if(s.inter is null, 0, s.inter) as s_inter
from
  stat_dir as d
  left join stat_zone as z on z.id_stat_zone=d.id_stat_zone
  left join tmp_r as r on r.id_stat_zone= d.id_stat_zone and r.zone=d.zone and r.dir=d.dir 
  left join tmp_s as s on s.id_stat_zone=d.id_stat_zone and s.zone=d.zone and s.dir=d.dir 
order by
  d.id_stat_zone, d_dir
