逐日實收分途徑統計表

select 日期,所屬分公司,所屬管理站,收費方式,SUM(實收金額) as 實收金額

      from(select to_char(ANA_SF_DATE,'yyyy-mm-dd') 日期,

                  to_nchar(sf.ana_sf_fgs) 所屬分公司,

                  to_nchar(sf.ana_sf_fwz) 所屬管理站,

                  ANA_SF_SJJKTYPE 收費方式,

                  SUM(ANA_SF_SSXJ) 實收金額

                  from ana_sf sf

                  where ana_sf_date>=

                  to_date('2009-02-02','yyyy-mm-dd')

                  and ana_sf_date<

                  to_date('2012-02-02','yyyy-mm-dd')+1

                  and nvl(ana_sf_fwz_sf,1) like '%'

                  and nvl(ana_sf_fgs_sf,1) like '%'

                  and (Ana_sf_fyjlzt in('取消收費','已收費','抹帳') or ana_sf_fyxm in('購氣','退氣','部費'))

                  and (nvl(ana_sf_org_id,'00') like left('00',6)||'%' or

                  nvl(ana_sf_org_id_sf,'00') like

                  left ('00',6) ||'%')

               GROUP BY to_nchar(sf.ana_sf_fgs),

                  to_nchar(sf.ana_sf_fwz),

                  to_char(ANA_SF_DATE, 'yyyy-mm-dd'),

                  ANA_SF_SJJKTYPE

                            union all

                            select to_char(ana_zjzhmx_date,'yyyy-mm-dd') 日期,

                            ana_zjzhmx_fgs 所屬公司,

                            ana_zjzhmx_fwz 所屬管理站,

                             ANA_ZJZHMX_FKFS 收費方式,

                             sum((case 

                                     when ana_zjzhmx_sfflag='收' then

                                       1

                                       else

                                         -1

                                         end

                             )* ana_zjzhmx_je ) 實收金額

                             from ana_zjzhmx

                             where ANA_ZJZHMX_FLAG='預存款'

                             and abs(ana_zjzhmx_je)>0.0

                             and ana_zjzhmx_date<

                             to_date('2009-02-02','yyyy-mm-dd')

                             to_date('2009-02-02','yyyy-mm-dd')+1

                         AND nvl(ANA_ZJZHMX_FWZ_sf, 1) like '%'

           and nvl(ANA_ZJZHMX_FGS_sf, 1) like '%'

           AND (nvl(ANA_ZJZHMX_ORG_ID, '00') like

               left('00', 6) || '%' or

               nvl(ANA_ZJZHMX_ORG_ID_SF, '00') like

               left('00', 6) || '%')

         GROUP BY ana_zjzhmx_fgs,

                  ana_zjzhmx_fwz,

                  to_char(ANA_ZJZHMX_DATE, 'yyyy-mm-dd'),

                  ANA_ZJZHMX_FKFS)

 group by 所屬分公司, 日期, 所屬管理站, 收費方式

本文轉自楊海龍的部落格部落格51CTO部落格，原文連結http://blog.51cto.com/7218743/1441596如需轉載請自行聯系原作者

IT達仁