
02.16 17:50:03.993 Started
     
 creatcreate temporary table tmp_dsv select  sd.id_stat_zone as id_stat_zone, 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  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 left(t.netindex,3) in ('141','145','152','154','213','244','339') and r_date>='2012-12-01' and r_date<='2013-01-31' group by sd.id_stat_zone
     
 create tablecreate table tmp_dz_r as  select  sd.id_stat_zone as id_stat_zone, sum(if(t.category='', 1, 0)) as r_zabugor  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=t.r_zone  where  r_date between '2012-12-01' and '2013-01-31'  and t.category!=''  and r_zone<>'206' and r_direct<>'-10'  group by id_stat_zone
     
 create tablcreate table tmp_dz_s as  select  sd.id_stat_zone as id_stat_zone, sum(if(t.category='', 1, 0)) as s_zabugor  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.s_zone  where  s_date between '2012-12-01' and '2013-01-31'  and t.category!=''  and s_zone<>'206' and s_direct<>'-10'  group by id_stat_zone
    
 creacreate temporary table tmp_r select  sd.id_stat_zone as id_stat_zone, 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
  ,   
 selecselect  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
   
 creacreate temporary table tmp_trans select  0 as id_stat_zone, count(*) as total, sum(if(zs.country>1 or zr.country>1 or t.category like '%', 1, 0)) as inter from tlg as t left join zone as zs on zs.zone=left(t.netindex, 3) left join zone as zr on zr.zone=t.source_cks where s_date>='2012-12-01' and s_date<='2013-01-31' and r_zone not in('205', '000')  and s_zone not in('205', '000')
    
 create tempocreate temporary table tmp_s select  sd.id_stat_zone as id_stat_zone, 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>='2012-12-01' and s_date<='2013-01-31' group by sd.id_stat_zone
    
   select
  z.name as z_name
, 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(d.total is null, 0, d.total-d.serv-d.avtokss-d.sending-d.repeat) as dsv
, 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
, if(t.total is null, 0, t.total) as t_total
, if(t.total is null, 0, t.total-t.inter) as t_ross
, 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
, if(dz_r.r_zabugor is null,0,dz_r.r_zabugor) as r_dz
, if(dz_s.s_zabugor is null,0,dz_s.s_zabugor) as s_dz
from
  stat_zone as z 
  left join tmp_r as r on r.id_stat_zone=z.id_stat_zone
  left join tmp_dsv as d on d.id_stat_zone=z.id_stat_zone
  left join tmp_trans as t on t.id_stat_zone=z.id_stat_zone
  left join tmp_s as s on s.id_stat_zone=z.id_stat_zone
  left join tmp_dz_r as dz_r on dz_r.id_stat_zone=z.id_stat_zone
  left join tmp_dz_s as dz_s on dz_s.id_stat_zone=z.id_stat_zone
order by
  z.id_stat_zone

02.16 17:51:50.345 Stopped

06.08 16:56:48.689 Started
     
 creatcreate temporary table tmp_dsv select  sd.id_stat_zone as id_stat_zone, 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  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 left(t.netindex,3) in ('135','141','145','152','154','213','244','339') and r_date>='2015-05-01' and r_date<='2015-05-31' group by sd.id_stat_zone
     
 create tablecreate table tmp_dz_r as  select  sd.id_stat_zone as id_stat_zone, sum(if(t.category='', 1, 0)) as r_zabugor  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=t.r_zone  where  r_date between '2015-05-01' and '2015-05-31'  and t.category!=''  and r_zone<>'206' and r_direct<>'-10'  group by id_stat_zone
     
 create tablcreate table tmp_dz_s as  select  sd.id_stat_zone as id_stat_zone, sum(if(t.category='', 1, 0)) as s_zabugor  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.s_zone  where  s_date between '2015-05-01' and '2015-05-31'  and t.category!=''  and s_zone<>'206' and s_direct<>'-10'  group by id_stat_zone
    
 creacreate temporary table tmp_r select  sd.id_stat_zone as id_stat_zone, 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
  ,   
 selecselect  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
   
 creacreate temporary table tmp_trans select  0 as id_stat_zone, count(*) as total, sum(if(zs.country>1 or zr.country>1 or t.category like '%', 1, 0)) as inter from tlg as t left join zone as zs on zs.zone=left(t.netindex, 3) left join zone as zr on zr.zone=t.source_cks where s_date>='2015-05-01' and s_date<='2015-05-31' and r_zone not in('244', '000')  and s_zone not in('244', '000')
    
 create tempocreate temporary table tmp_s select  sd.id_stat_zone as id_stat_zone, 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
    
   select
  z.name as z_name
, 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(d.total is null, 0, d.total-d.serv-d.avtokss-d.sending-d.repeat) as dsv
, 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
, if(t.total is null, 0, t.total) as t_total
, if(t.total is null, 0, t.total-t.inter) as t_ross
, 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
, if(dz_r.r_zabugor is null,0,dz_r.r_zabugor) as r_dz
, if(dz_s.s_zabugor is null,0,dz_s.s_zabugor) as s_dz
from
  stat_zone as z 
  left join tmp_r as r on r.id_stat_zone=z.id_stat_zone
  left join tmp_dsv as d on d.id_stat_zone=z.id_stat_zone
  left join tmp_trans as t on t.id_stat_zone=z.id_stat_zone
  left join tmp_s as s on s.id_stat_zone=z.id_stat_zone
  left join tmp_dz_r as dz_r on dz_r.id_stat_zone=z.id_stat_zone
  left join tmp_dz_s as dz_s on dz_s.id_stat_zone=z.id_stat_zone
order by
  z.id_stat_zone

06.08 16:56:53.165 Stopped

06.08 17:04:55.178 Started
     
 creatcreate temporary table tmp_dsv select  sd.id_stat_zone as id_stat_zone, 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  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 left(t.netindex,3) in ('135','141','145','152','154','213','244','339') and r_date>='2015-04-01' and r_date<='2015-04-30' group by sd.id_stat_zone
     
 create tablecreate table tmp_dz_r as  select  sd.id_stat_zone as id_stat_zone, sum(if(t.category='', 1, 0)) as r_zabugor  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=t.r_zone  where  r_date between '2015-04-01' and '2015-04-30'  and t.category!=''  and r_zone<>'206' and r_direct<>'-10'  group by id_stat_zone
     
 create tablcreate table tmp_dz_s as  select  sd.id_stat_zone as id_stat_zone, sum(if(t.category='', 1, 0)) as s_zabugor  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.s_zone  where  s_date between '2015-04-01' and '2015-04-30'  and t.category!=''  and s_zone<>'206' and s_direct<>'-10'  group by id_stat_zone
    
 creacreate temporary table tmp_r select  sd.id_stat_zone as id_stat_zone, 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-04-01' and r_date<='2015-04-30' group by sd.id_stat_zone
  ,   
 selecselect  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-04-01' and r_date<='2015-04-30' and r_zone='244' and r_direct like '9__' and rao_index is not null
   
 creacreate temporary table tmp_trans select  0 as id_stat_zone, count(*) as total, sum(if(zs.country>1 or zr.country>1 or t.category like '%', 1, 0)) as inter from tlg as t left join zone as zs on zs.zone=left(t.netindex, 3) left join zone as zr on zr.zone=t.source_cks where s_date>='2015-04-01' and s_date<='2015-04-30' and r_zone not in('244', '000')  and s_zone not in('244', '000')
    
 create tempocreate temporary table tmp_s select  sd.id_stat_zone as id_stat_zone, 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-04-01' and s_date<='2015-04-30' group by sd.id_stat_zone
    
   select
  z.name as z_name
, 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(d.total is null, 0, d.total-d.serv-d.avtokss-d.sending-d.repeat) as dsv
, 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
, if(t.total is null, 0, t.total) as t_total
, if(t.total is null, 0, t.total-t.inter) as t_ross
, 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
, if(dz_r.r_zabugor is null,0,dz_r.r_zabugor) as r_dz
, if(dz_s.s_zabugor is null,0,dz_s.s_zabugor) as s_dz
from
  stat_zone as z 
  left join tmp_r as r on r.id_stat_zone=z.id_stat_zone
  left join tmp_dsv as d on d.id_stat_zone=z.id_stat_zone
  left join tmp_trans as t on t.id_stat_zone=z.id_stat_zone
  left join tmp_s as s on s.id_stat_zone=z.id_stat_zone
  left join tmp_dz_r as dz_r on dz_r.id_stat_zone=z.id_stat_zone
  left join tmp_dz_s as dz_s on dz_s.id_stat_zone=z.id_stat_zone
order by
  z.id_stat_zone

06.08 17:04:59.214 Stopped
