select region,system, m.period,period_date,sch,act,cumsch,cumact,runrate,cardstodo from 
(select region,system,sysid,period,period_date,runrate,cardstodo from admin0.zv_vinny_runrate z, 
(
select c.period,x.period_date from 
(select week_name as period from admin0.calendar where 
period_date between 
 {d '2001-07-29'} and {d '2002-03-31'}
 group by week_name 
union all
 select 0 as period from admin0.calendar where period_date = {d '2001-07-28'}) c,
(select period_date,week_name as period from admin0.calendar where week_end = 1
  union all select period_date,0 as period from admin0.calendar 
where period_date = {d '2001-07-28'}) x
where c.period=x.period) cc
) m,
 (
select sysid,week_name as period,count(*) as sch, cast(NULL as integer) as cumsch
 from admin0.g3data d,admin0.calendar c 
where btins_S=period_date and btins_S between 
 {d '2001-07-29'} and {d '2002-03-31'}  
group by sysid,week_name
 union all 
select sysid,0 as period,cast(NULL as integer) as sch, count(btins_S) as cumsch from admin0.g3data 
where btins_S < {d '2001-07-29'} group by sysid
) s,
(
select sysid,0 as period,cast(NULL as integer) as act,count(BTINS_A) as cumact from admin0.g3data
 where BTINS_A < {d '2001-07-29'} group by sysid union all
 select sysid,week_name as period,count(*) as act, cast(NULL as integer) as cumact  from admin0.g3data d,admin0.calendar c
 where BTINS_A=period_date and BTINS_A between 
 {d '2001-07-29'} and {d '2002-03-31'} 
 group by sysid,week_name
 )  a
  where s.sysid (+) = m.sysid and s.period (+) = m.period
 and a.sysid (+) = m.sysid and a.period (+) = m.period
and m.sysid  in ('BU03','DE03','DN03','BS03','PL03','CX07','CX06')
UNION ALL
 select m.system as region,m.region as system,m.period,m.period_date,sch,act,cumsch,cumact,runrate,cardstodo from 
(
select region,system,period,period_date,(runrate*5) as runrate,cardstodo  from 
(select region,' SUMMARY' as system,sum(runrate) as runrate,sum(cardstodo) as cardstodo
 from admin0.zv_vinny_runrate group by region) z,
(
select c.period,x.period_date from 
(select week_name as period from admin0.calendar where 
period_date between 
 {d '2001-07-29'} and {d '2002-03-31'}
 group by week_name 
union all
 select 0 as period from admin0.calendar where period_date = {d '2001-07-28'}) c,
(select period_date,week_name as period from admin0.calendar where week_end = 1
  union all select period_date,0 as period from admin0.calendar 
where period_date = {d '2001-07-28'}) x
where c.period=x.period) cc
) m,
(
select region,' SUMMARY' as system,week_name as period,count(BTINS_s) as sch,
cast(NULL as integer) as cumsch 
from admin0.g3data  t,
admin0.zv_sprintSystemsByRegion z,
admin0.calendar c
 where t.sysid=z.sysid
and  BTINS_S=period_date and BTINS_S between {d '2001-07-29'} and {d '2002-03-31'}  
group by region, week_name
union all
select region,' SUMMARY' as system,0  as period,cast(NULL as integer) as sch,count(BTINS_s) as cumsch 
from admin0.g3data  t,
admin0.zv_sprintSystemsByRegion z,
admin0.calendar c
 where t.sysid=z.sysid
and  BTINS_S=period_date and BTINS_S < {d '2001-07-29'}  
group by region, week_name
)  ss,
(
select region,' SUMMARY' as system,week_name as period,count(BTINS_A) as act,
cast(NULL as integer) as cumact 
from admin0.g3data  t,
admin0.zv_sprintSystemsByRegion z,
admin0.calendar c
 where t.sysid=z.sysid
and  BTINS_A=period_date and BTINS_A between 
 {d '2001-07-29'} and {d '2002-03-31'}  
 group by region, week_name
union all
select region,' SUMMARY' as system,0  as period,cast(NULL as integer) as act,count(BTINS_A) as cumact 
from admin0.g3data  t,
admin0.zv_sprintSystemsByRegion z,
admin0.calendar c
 where t.sysid=z.sysid
and  BTINS_A=period_date and BTINS_A < {d '2001-07-29'}  
group by region, week_name
)  aa
 where ss.region (+) = m.region and ss.period (+) = m.period
and  aa.region (+) = m.region and aa.period (+) = m.period
UNION ALL
select m.region,m.system,m.period,m.period_date,sch,act,cumsch,cumact,runrate,cardstodo from 
(select region,system,period,period_date,(runrate*5) as runrate,cardstodo from 
(select ' SUMMARY' as region,' SUMMARY' as system, sum(runrate) as runrate, sum(cardstodo) as cardstodo 
from admin0.zv_vinny_runrate) z,
(
select c.period,x.period_date from 
(select week_name as period from admin0.calendar where 
period_date between {d '2001-07-29'} and {d '2002-03-31'}  group by week_name 
union all
 select 0 as period from admin0.calendar where period_date = {d '2001-07-28'}) c,
(select period_date,week_name as period from admin0.calendar where week_end = 1
  union all select period_date,0 as period from admin0.calendar 
where period_date = {d '2001-07-28'}) x
where c.period=x.period)) m,
(
select ' SUMMARY' as region,' SUMMARY' as system,week_name as period,count(BTINS_s) as sch,
cast(NULL as integer) as cumsch 
from admin0.g3data  t,
admin0.calendar c
 where BTINS_S=period_date and BTINS_S between 
 {d '2001-07-29'} and {d '2002-03-31'}
 group by week_name
union all
select ' SUMMARY' as region,' SUMMARY' as system,0  as period,cast(NULL as integer) as sch,count(BTINS_s) as cumsch 
from admin0.g3data  t,
admin0.calendar c
 where BTINS_S=period_date and BTINS_S < {d '2001-07-29'}  
group by week_name) ss,
(
select ' SUMMARY' as region,' SUMMARY' as system,week_name as period,count(BTINS_a) as act,
cast(NULL as integer) as cumact 
from admin0.g3data  t,
admin0.calendar c
 where BTINS_A=period_date and BTINS_A between {d '2001-07-29'} and {d '2002-03-31'}
group by week_name
union all
select ' SUMMARY' as region,' SUMMARY' as system,0  as period,cast(NULL as integer) as act,count(BTINS_A) as cumact 
from admin0.g3data  t,
admin0.calendar c
 where BTINS_A=period_date and BTINS_A < {d '2001-07-29'}  
group by week_name) aa
 where ss.region (+) = m.region and ss.period (+) = m.period
and  aa.region (+) = m.region and aa.period (+) = m.period
order by region,system,period