select report_date,
c.SERVICE_TYPE_ID,
c.service_type_name,
FEE,
num,
TIMES,
telTime,
SECONDS,
CITYTIMES,
SERVICETIMES
from (select nvl(period_id, '其它') REPORT_DATE,
b.service_type_id,
sum(FEE) FEE,
count(*) TIMES,
count(distinct calling_id) num,
sum(DURATION) SECONDS,
sum(ceil(duration / 60)) telTime,
sum(LOCAL_UNITS) CITYTIMES,
sum(charge_units) SERVICETIMES
from (select sp_id,
calling_id,
owner_area_code,
dr_day,
period_id,
prod_id,
service_id,
test_flag,
service_type_id,
fee,
duration,
LOCAL_UNITS,
charge_units,
acct_TYPE_ID,
location_id,
dr_source
from DAT_DR_200701
where dr_day between '20070118' and '20070405'
union all
select sp_id,
calling_id,
owner_area_code,
dr_day,
period_id,
prod_id,
service_id,
test_flag,
service_type_id,
fee,
duration,
LOCAL_UNITS,
charge_units,
acct_TYPE_ID,
location_id,
dr_source
from DAT_DR_200702
where dr_day between '20070118' and '20070405'
union all
select sp_id,
calling_id,
owner_area_code,
dr_day,
period_id,
prod_id,
service_id,
test_flag,
service_type_id,
fee,
duration,
LOCAL_UNITS,
charge_units,
acct_TYPE_ID,
location_id,
dr_source
from DAT_DR_200703
where dr_day between '20070118' and '20070405'
union all
select sp_id,
calling_id,
owner_area_code,
dr_day,
period_id,
prod_id,
service_id,
test_flag,
service_type_id,
fee,
duration,
LOCAL_UNITS,
charge_units,
acct_TYPE_ID,
location_id,
dr_source
from DAT_DR_200704
where dr_day between '20070118' and '20070405') a,
cfg_service b,
cfg_prod_service prodService
where a.prod_id = prodService.prod_id
and a.service_id = b.service_id
and prodService.service_id = b.service_id
and a.location_id in ('010')
and a.dr_source = '01'
group by period_id, b.SERVICE_type_ID) result,
cfg_service_type c
where result.service_type_id = c.service_type_id
order by REPORT_DATE, SERVICE_TYPE_ID, FEE desc