17734344133(提问者)SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <单据状态统计表,,>
-- =============================================
alter PROCEDURE hy_djzttjb
create table #a
( nid varchar(100),
djmc varchar(100),
djlx varchar(100),
zc decimal(18,0) default(0),
cj decimal(18,0) default(0),
shz decimal(18,0) default(0),
cxsh decimal(18,0) default(0),
ysh decimal(18,0) default(0),
zsl decimal(18,0) default(0)
)
---采购申请单
insert into #a(nid,djmc,djlx,zc,cj,shz,cxsh,ysh)
select \'01\',\'采购申请单\',\'标准采购申请\',case when v.FDOCUMENTSTATUS=\'Z\' then count(v.FDOCUMENTSTATUS) else 0 end as zc,
case when v.FDOCUMENTSTATUS=\'A\' then count(v.FDOCUMENTSTATUS) else 0 end as cj,
case when v.FDOCUMENTSTATUS=\'B\' then count(v.FDOCUMENTSTATUS) else 0 end as shz,
case when v.FDOCUMENTSTATUS=\'D\' then count(v.FDOCUMENTSTATUS) else 0 end as cxsh,
case when v.FDOCUMENTSTATUS=\'C\' then count(v.FDOCUMENTSTATUS) else 0 end as ysh
from T_PUR_Requisition v
left join T_ORG_Organizations o on o.forgid=v.FAPPLICATIONORGID
left join T_ORG_Organizations_L o1 on o.forgid=o1.forgid
where v.fbilltypeid=\'93591469feb54ca2b08eb635f8b79de3\'
and v.FAPPLICATIONDATE>=@FDateks
and v.FAPPLICATIONDATE<=@FDatejs
and o1.fname = @zzmc@
group by v.FDOCUMENTSTATUS
update #a set zsl=zc+cj+shz+cxsh+ysh
select row_number() over (order by nid) as 序号,djmc 单据,djlx 单据类型,sum(zc) as zc ,sum(cj) as cj,sum(shz) as shz,sum(cxsh) as cxsh,sum(ysh) as ysh,sum(zsl) as zsl
from #a
group by nid,djmc,djlx
order by nid
