CREATE PROCEDURE ERP_Efficiency @begdate varchar(10), @enddate varchar(10), @workshop varchar(10) AS BEGIN SET NOCOUNT ON declare @s varchar(8000) set @s = 'select isnull(物料编码,''合计'') as 物料编码,物料名称'
select @s = @s+',['+a.日期+']=sum(case when convert(char(10),日期,120)='''+a.日期+''' then convert(decimal(9,0),实收数) end)' from (select distinct convert(char(10),日期,120) as 日期 from erp_vwicbill_2 where convert(char(10),日期,120) between @begdate and @enddate) a
set @s = @s + ',小计=sum(convert(decimal(9,0),实收数)) from erp_vwicbill_2 where 生产车间='''+@workshop + ''' group by 物料编码,物料名称 with rollup having grouping(物料名称)=0 or grouping(物料编码)=1 order by 物料编码 ' exec(@s) END GO
@begdate varchar(10),
@enddate varchar(10),
@workshop varchar(10)
AS
BEGIN
SET NOCOUNT ON
declare @s varchar(8000)
set @s = 'select isnull(物料编码,''合计'') as 物料编码,物料名称'
select
@s = @s+',['+a.日期+']=sum(case when convert(char(10),日期,120)='''+a.日期+''' then convert(decimal(9,0),实收数) end)'
from
(select distinct convert(char(10),日期,120) as 日期 from erp_vwicbill_2 where convert(char(10),日期,120) between @begdate and @enddate) a
set @s = @s + ',小计=sum(convert(decimal(9,0),实收数)) from erp_vwicbill_2 where 生产车间='''+@workshop
+ ''' group by 物料编码,物料名称 with rollup having grouping(物料名称)=0 or grouping(物料编码)=1 order by 物料编码 '
exec(@s)
END
GO