USE [BM]
GO
/****** Object: StoredProcedure [dbo].[zsp_weekneedata] Script Date: 2015-12-19 15:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[zsp_weekneedata] @startdate char(8),@enddate char(8),@s char(1),@count int
--开始日期,结束日期,是否显示仓库,指定周期即循环数
as
declare @tempdate char(8),@sql varchar(8000),@SQL1 VARCHAR(8000)--,@enddate char(8),@startdate char(8) varchar(8000)最长字符8000
SElect @SQL1=' select TB003 ',@tempdate= @startdate,@sql=''
while (@enddate>=@tempdate)
BEGIN
set @sql=@sql+',isnull(sum(case when TA009>='''+@tempdate + ''' and TA009<CONVERT( char(8), dateadd(dd,7,'''+@tempdate+'''),112) then isnull(TB004-TB005,0) end),0) AS WEEK'+CONVERT(CHAR(8),@count)
set @count= @count+1
set @tempdate=CONVERT( char(8), dateadd(dd,7,@tempdate),112)
end
set @SQL1=@SQL1+@sql+',sum(TB004-TB005) AS 总需领量 FROM MOCTA LEFT JOIN MOCTB ON TA001=TB001 AND TA002=TB002 WHERE TA011 NOT IN (''y'',''Y'') and TB004-TB005>=0 and TA009>= '''+@startdate+''' and TA009<'''+@enddate+''' group by TB003 ' --
if(@s='0')--值为0省略仓库,值为1添加仓库列
exec( 'select C.MB002 as 品名,C.MB003 as 规格,B.SQTY as 库存数, A.*,B.SQTY-A.总需领量 AS 结余数 from ('+@SQL1 +') A right join (select A.MC001,SUM(A.MC007) AS SQTY from INVMC A left join CMSMC B ON A.MC002=B.MC001
WHERE B.MC004=''1'' and B.MC005=''Y''
group by A.MC001) B ON B.MC001=A.TB003 LEFT JOIN INVMB C ON MB001=A.TB003 WHERE C.MB005=''02'' order by A.TB003' )
else
exec( 'select C.MB002 as 品名,C.MB003 as 规格,B.SQTY as 库存数,A.*,D.不良品仓,D.借出仓,B.SQTY+D.不良品仓+D.借出仓-A.总需领量 AS 结余数 from ('+@SQL1 +') A LEFT join (select A.MC001,SUM(A.MC007) AS SQTY from INVMC A left join CMSMC B ON A.MC002=B.MC001
WHERE B.MC004=''1'' and B.MC005=''Y''
group by A.MC001) B ON B.MC001=A.TB003 LEFT JOIN INVMB C ON MB001=A.TB003 left join (SELECT MC001,SUM(CASE WHEN MC002=''CA01'' THEN MC007 ELSE 0 END) AS ''不良品仓'' ,
SUM(CASE WHEN MC002=''CA02'' THEN MC007 ELSE 0 END) AS ''借出仓'' FROM INVMC WHERE MC002 IN(''CA02'',''CA01'') group by MC001 ) D ON D.MC001=A.TB003 WHERE C.MB005=''02'' ORDER BY A.TB003')
GO
/****** Object: StoredProcedure [dbo].[zsp_weekneedata] Script Date: 2015-12-19 15:36:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[zsp_weekneedata] @startdate char(8),@enddate char(8),@s char(1),@count int
--开始日期,结束日期,是否显示仓库,指定周期即循环数
as
declare @tempdate char(8),@sql varchar(8000),@SQL1 VARCHAR(8000)--,@enddate char(8),@startdate char(8) varchar(8000)最长字符8000
SElect @SQL1=' select TB003 ',@tempdate= @startdate,@sql=''
while (@enddate>=@tempdate)
BEGIN
set @sql=@sql+',isnull(sum(case when TA009>='''+@tempdate + ''' and TA009<CONVERT( char(8), dateadd(dd,7,'''+@tempdate+'''),112) then isnull(TB004-TB005,0) end),0) AS WEEK'+CONVERT(CHAR(8),@count)
set @count= @count+1
set @tempdate=CONVERT( char(8), dateadd(dd,7,@tempdate),112)
end
set @SQL1=@SQL1+@sql+',sum(TB004-TB005) AS 总需领量 FROM MOCTA LEFT JOIN MOCTB ON TA001=TB001 AND TA002=TB002 WHERE TA011 NOT IN (''y'',''Y'') and TB004-TB005>=0 and TA009>= '''+@startdate+''' and TA009<'''+@enddate+''' group by TB003 ' --
if(@s='0')--值为0省略仓库,值为1添加仓库列
exec( 'select C.MB002 as 品名,C.MB003 as 规格,B.SQTY as 库存数, A.*,B.SQTY-A.总需领量 AS 结余数 from ('+@SQL1 +') A right join (select A.MC001,SUM(A.MC007) AS SQTY from INVMC A left join CMSMC B ON A.MC002=B.MC001
WHERE B.MC004=''1'' and B.MC005=''Y''
group by A.MC001) B ON B.MC001=A.TB003 LEFT JOIN INVMB C ON MB001=A.TB003 WHERE C.MB005=''02'' order by A.TB003' )
else
exec( 'select C.MB002 as 品名,C.MB003 as 规格,B.SQTY as 库存数,A.*,D.不良品仓,D.借出仓,B.SQTY+D.不良品仓+D.借出仓-A.总需领量 AS 结余数 from ('+@SQL1 +') A LEFT join (select A.MC001,SUM(A.MC007) AS SQTY from INVMC A left join CMSMC B ON A.MC002=B.MC001
WHERE B.MC004=''1'' and B.MC005=''Y''
group by A.MC001) B ON B.MC001=A.TB003 LEFT JOIN INVMB C ON MB001=A.TB003 left join (SELECT MC001,SUM(CASE WHEN MC002=''CA01'' THEN MC007 ELSE 0 END) AS ''不良品仓'' ,
SUM(CASE WHEN MC002=''CA02'' THEN MC007 ELSE 0 END) AS ''借出仓'' FROM INVMC WHERE MC002 IN(''CA02'',''CA01'') group by MC001 ) D ON D.MC001=A.TB003 WHERE C.MB005=''02'' ORDER BY A.TB003')