(@CheckDate smalldatetime)
RETURNS @FlowSubsistenceList TABLE
(ProductID nvarchar(6),
ProductName nvarchar(32),
HJGoodQty real,
HJTodayQty real,
HJYesterdayQty real,
HJDefference real,
SXGoodQty real,
SXBadQty real,
SXTodayQty real,
SXYesterdayQty real,
SXDefference real,
JTGoodQty real,
JTBadQty real,
JTTodayQty real,
JTYesterdayQty real,
JTDefference real,
PHGoodQty real,
PHBadQty real,
PHTodayQty real,
PHYesterdayQty real,
PHDefference real,
ZJGoodQty real,
ZJBadQty real)
AS
BEGIN
DECLARE @ProductIDList TABLE
(ProductID nvarchar(6))
DECLARE @YesterdayOfCheckDate smalldatetime
SET @YesterdayOfCheckDate = DATEADD(day, -1, @CheckDate)
--保存该日生产的产品ID号
INSERT @ProductIDList
select distinct LEFT(productid, 4) + '00' as productid from yuancaiFlow_view
where productiondate = @CheckDate
and productid like '01____'
--正常工序为hj->sx->jt->ph->zj
INSERT @FlowSubsistenceList
select a.productid,
b.productname,
isnull(hj.GoodQty, 0) as hj_good,
isnull(hjtoday.qty, 0) as hj_today,
isnull(hjyesterday.qty, 0) hj_yesterday,
0 - (isnull(hj.GoodQty, 0) - isnull(hjtoday.qty, 0) + isnull(hjyesterday.qty, 0) - isnull(sx.GoodQty, 0) - isnull(sx.BadQty, 0)) as hj_difference, |
