删除set_item表中重复记录、按收费标准及付款方式分类汇总自动变换报表

--删除set_item表中重复记录

select identity(int,1,1) as autoID, * into #Tmp from set_item

select min(autoID) as autoID into #Tmp2 from #Tmp group by tablename,itemname

drop table set_item

select ID,tablename,itemname,flag,explain into set_item from #Tmp where autoID in(select autoID from #tmp2)

drop table #Tmp

drop table #Tmp2

 

--

alter Procedure [dbo].[p_paydetail_b]
@as_yearnum char(8),@ad_start date,@ad_end date
as
select
 convert(date,t_paydetail.kpdate) kpdate,
 t_chargemode.Unitprice,
 t_paymode.Name,
 isnull(p.thisaccount,0) thisaccount,
 isnull(p.billcount,0) billcount,
 isnull(p.oldowe,0) oldowe,
 isnull(g.gzf,0) gzf
from  
 (select distinct kpdate,yearnum from paydetail where kpdate >= @ad_start and kpdate <= @ad_end) as t_paydetail
 left outer join
 (select convert(date,DATE) date,sum(gzf) gzf from t_gzf group by convert(date,date)) g
 on convert(date,t_paydetail.kpdate) = g.date
 cross join
 (select unitprice from chargemode) as t_chargemode
 cross join
 (select paymodeid,name from paymode
 union
 select 'a','发票数'
 union
 select 'b','陈欠费') as t_paymode
 left outer join
 (select kpdate,Chargestandard,PayModeid,sum(Thisaccount) thisaccount,
   billcount = sum(
    case
     when billtype = '发票' then 1
    else 0
    end),
   oldowe = sum(case when yearnum < @as_yearnum then Thisaccount else 0 end )
 from paydetail
 group by kpdate,Chargestandard,PayModeid
 ) p
 on t_paydetail.kpdate = p.kpdate and
  t_chargemode.Unitprice = p.Chargestandard and
  t_paymode.PayModeId = p.PayModeid
order by convert(date,t_paydetail.kpdate),t_chargemode.Unitprice,t_paymode.PayModeId

 

 

 

--最终标准  付款方式存储过程

alter Procedure [dbo].[p_paydetail_b]
@as_yearnum char(8),@ad_start date,@ad_end date
as
select t_a.kpdate,
 t_a.Unitprice,
 t_a.Name,
 t_a.gzf,
 thisaccount = isnull((
    case
     when paymodeid = 'a' then t_b.billcount
     when paymodeid = 'b' then t_b.oldowe
    else t_a.Thisaccount
    end),0)
 from

(select
 convert(date,t_paydetail.kpdate) kpdate,
 t_chargemode.Unitprice,
 t_paymode.Name,
 t_paymode.PayModeId,
 isnull(p.thisaccount,0) thisaccount,
 isnull(g.gzf,0) gzf
from  
 (select distinct kpdate,yearnum from paydetail where kpdate >= @ad_start and kpdate <= @ad_end) as t_paydetail
 left outer join
 (select convert(date,DATE) date,sum(gzf) gzf from t_gzf group by convert(date,date)) g
 on convert(date,t_paydetail.kpdate) = g.date
 cross join
 (select unitprice from chargemode) as t_chargemode
 cross join
 (select paymodeid,name from paymode) as t_paymode
 left outer join
 (select kpdate,Chargestandard,PayModeid,
   sum(Thisaccount) thisaccount
 from paydetail
 group by kpdate,Chargestandard,PayModeid
 ) p
 on t_paydetail.kpdate = p.kpdate and
  t_chargemode.Unitprice = p.Chargestandard and
  t_paymode.PayModeId = p.PayModeid

) t_a
left outer join
(select 'a' a,kpdate,Chargestandard,
   billcount = sum(
    case
     when billtype = '发票' then 1
    else 0
    end),
   oldowe = sum(case when yearnum < @as_yearnum then Thisaccount else 0 end )
   
from paydetail
group by kpdate,Chargestandard) t_b
on t_a.kpdate = t_b.kpdate and t_a.Unitprice = t_b.Chargestandard
order by convert(date,t_a.kpdate),t_a.Unitprice,t_a.PayModeId