上个月做的报表

记下这个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,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) tot,--排序用，因为跟进率是字符串，所以还是要再做个字段出来做排序
       (nvl(c.sthours,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--这里最重要，这样就能有以日期为统计了。