Subversion Repositories general

Compare Revisions

Ignore whitespace Rev 938 → Rev 939

/sun/TrafficCount/trunk/sql/report_per_month2.sql
0,0 → 1,69
select
d.month,
round((
(c2.base+c2.delta) - (c3.base+c3.delta)
+ (d2.base+d2.delta) - (d3.base+d3.delta)
) / 1024 / 1024) as count
from
( select 1 as month
union select 2 as month
union select 3 as month
union select 4 as month
union select 5 as month
union select 6 as month
union select 7 as month
union select 8 as month
union select 9 as month
union select 10 as month
union select 11 as month
union select 12 as month
) as d
left join tcount c2
on c2.source = 'fw_in'
and c2.host = 'mercury'
and c2.stamp = (
select max(stamp) as max_stamp
from tcount c1
where c1.source = 'fw_in'
and c1.host = 'mercury'
and c1.stamp >= date(d.month || '/1/2003')
and c1.stamp < date(d.month || '/1/2003') + interval '1 month'
group by date_trunc('month', c1.stamp)
)
left join tcount c3
on c3.source = 'fw_in'
and c3.host = 'mercury'
and c3.stamp = (
select max(stamp) as max_stamp
from tcount c4
where c4.source = 'fw_in'
and c4.host = 'mercury'
and c4.stamp >= date(d.month || '/1/2003') - interval '1 month'
and c4.stamp < date(d.month || '/1/2003')
group by date_trunc('month', c4.stamp)
)
left join tcount d2
on d2.source = 'fw_out'
and d2.host = 'mercury'
and d2.stamp = (
select max(stamp) as max_stamp
from tcount d1
where d1.source = 'fw_out'
and d1.host = 'mercury'
and d1.stamp >= date(d.month || '/1/2003')
and d1.stamp < date(d.month || '/1/2003') + interval '1 month'
group by date_trunc('month', d1.stamp)
)
left join tcount d3
on d3.source = 'fw_out'
and d3.host = 'mercury'
and d3.stamp = (
select max(stamp) as max_stamp
from tcount d4
where d4.source = 'fw_out'
and d4.host = 'mercury'
and d4.stamp >= date(d.month || '/1/2003') - interval '1 month'
and d4.stamp < date(d.month || '/1/2003')
group by date_trunc('month', d4.stamp)
)
order by d.month;