alter PROCEDURE [dbo].[tj_inoutstock]
(@ck nvarchar(16),
@datePd DATETIME,
@dateEnd datetime)
AS
declare @dateflt nvarchar(100)
IF @dateEnd = ''
SET @dateflt = '进出日期 > ' + @datePd
ELSE IF @datePd > '' AND @dateEnd = ''
SET @dateflt = '进出日期 > ' + @datePd
ELSE if @datePd > '' AND @dateEnd > ''
SET @dateflt = '进出日期 >' + @datePd + ' AND 进出日期 <' + @dateEnd
BEGIN
declare @sql nvarchar(max)
set @sql = 'sｅlect c.产品编码,C.品名,C.规格,c.工序号,c.仓库,c.盘点日期,c.实盘数量,d.本期入库,d.本期出库,d.本期变动,cast((isnull(实盘数量,0)+isnull(本期入库,0)-isnull(本期出库,0)) as decimal(18,2)) as 期末结存,cast((g.库存数量+g.不良数量+g.报废数量) as decimal(18,2)) As 当前数量
from
(SｅLECT b.产品编码,b.品名,b.规格,b.工序号,a.仓库,盘点日期,(实盘数量+实盘不良数量+实盘报废数量) As 实盘数量 FROM 盘点表 a inner join dbo.盘点明细 b on a.盘点编号 = b.盘点编号 and a.仓库 = ' +@ck + ' and 盘点日期 = @datePd
Union
SｅLECT 产品编码,品名,规格,工序号,仓库,'' AS 盘点日期,0 as 实盘数量 FROM 库存表
WHERE 仓库 = '+@ck + ' AND NOT exists (SｅLECT * FROM 盘点表 a inner join 盘点明细 b on a.盘点编号 = b.盘点编号 and a.仓库 = ' +@ck+ ' and 盘点日期 = @datePd AND 库存表.产品编码= b.产品编码 AND 库存表.工序号= b.工序号 AND 库存表.仓库= a.仓库)
) as c
LEFT JOIN
(sｅlect 产品编码,工序号,仓库,isnull(sum(入库数量),0) as 本期入库,isnull(sum(出库数量),0) as 本期出库,isnull(sum(进出数量),0) as 本期变动 from Stockinout
where 进出类别 <> ''盘点'' and 品质状态 <> ''未判定'' AND 仓库 = ' +@ck+ ' and ' + @dateflt + 'group by 产品编码,工序号,仓库) d
on c.产品编码 = d.产品编码 and c.工序号 = d.工序号 and c.仓库 = d.仓库
LEFT JOIN 库存表 g ON g.产品编码 = c.产品编码 AND g.仓库 = c.仓库 AND g.工序号 = c.工序号'
exec(@sql)
END
加好友 
发短信
Post By：2016/10/31 15:50:00 [显示全部帖子]
