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--这里最重要，这样就能有以日期为统计了。