oracle 报表 自己备份用
上个月做的报表
记下这个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--这里最重要，这样就能有以日期为统计了。??
?
?
?