oracle 报表 自己备份用

e04xx8l377

　　

　　上个月做的报表
　　记下这个SQL文，以后还要用。

select a.chldate,
nvl(a.Num_Assign, 0) tot_dis, --累计分配量
nvl(b.Num_Followup, 0) tot_followup, --累计跟进量
to_char(round((nvl(c.sthours, 0) + nvl(d.tfhours, 0) +
nvl(e.fehours, 0)) / nvl(a.Num_Assign, 0) * 100,
2)) || '%' st_rate, --72小时跟进率
to_char(round(nvl(b.Num_Followup, 0) / nvl(a.Num_Assign, 0) * 100, 2)) || '%' tot_rete, --累计跟进率
nvl(d.tfhours, 0) num_tf, --24小时跟进量
nvl(e.fehours, 0) num_fe, --48小时跟进量
nvl(c.sthours, 0) num_st, --72小时跟进量
nvl(g.moresthours, 0) num_more_st, --大于72小时跟进量
nvl(b.Num_Followup, 0) / nvl(a.Num_Assign, 0) tot, --排序用，因为跟进率是字符串，所以还是要再做个字段出来做排序
(nvl(c.sthours, 0) + nvl(d.tfhours, 0) + nvl(e.fehours, 0)) /
nvl(a.Num_Assign, 0) st --排序用，因为跟进率是字符串，所以还是要再做个字段出来做排序
from (select to_char(aa.CHLDISTRIBUTEDATE, 'yyyy-mm-dd') chldate,
count(1) Num_Assign
from club_hot_leads aa
where aa.flag = 1
and aa.chlisdistribute = 1
and aa.CHLDISTRIBUTEDATE >= to_date('2012-04-01', 'yyyy-mm-dd')
and aa.CHLDISTRIBUTEDATE <= to_date('2012-04-30', 'yyyy-mm-dd')
group by to_char(aa.CHLDISTRIBUTEDATE, 'yyyy-mm-dd')) a
left join (select count(1) Num_Followup,
to_char(bb.CHLDISTRIBUTEDATE, 'yyyy-mm-dd') chldate
from club_hot_leads bb
where bb.flag = 1
and bb.CHLDISTRIBUTEDATE >= to_date('2012-04-01', 'yyyy-mm-dd')
and bb.CHLDISTRIBUTEDATE <= to_date('2012-04-30', 'yyyy-mm-dd')
and bb.CHLMARKETINGDATE is not null
and bb.CHLMARKETINGSTATEID is not null
group by to_char(bb.CHLDISTRIBUTEDATE, 'yyyy-mm-dd')) b
on a.chldate = b.chldate--这里最重要，这样就能有以日期为统计了。