以下为安全库存预警SQL语句，如果需要指定具体仓库，如：01.01仓库，应该怎样修改？
SET nocount ON
SET ansi_warnings off
SELECT t4.fitemid,
Sum(t4.fqty) AS fqty
INTO #icinventory
FROM icinventory t4
INNER JOIN t_icitem t1
ON t1.fitemid = t4.fitemid
INNER JOIN t_stock t3
ON t4.fstockid = t3.fitemid
WHERE 1 = 1
AND t1.fdeleted = 0
GROUP BY t4.fitemid
INSERT INTO #icinventory
SELECT t4.fitemid,
Sum(0) AS fqty
FROM icinventory t4
INNER JOIN t_icitem t1
ON t1.fitemid = t4.fitemid
INNER JOIN t_stock t3
ON t4.fstockid = t3.fitemid
WHERE 1 = 1
AND t4.fitemid NOT IN (SELECT DISTINCT (fitemid)
FROM #icinventory)
AND t1.fdeleted = 0
GROUP BY t4.fitemid
INSERT INTO #icinventory
SELECT t1.fitemid,
0
FROM t_icitem t1
WHERE t1.fitemid NOT IN (SELECT DISTINCT (fitemid)
FROM icinventory)
AND t1.fdeleted = 0
SELECT t5.fname AS fclassname,
t2.fshortnumber,
t2.fnumber,
t2.fname,
t2.fmodel,
t2.fqtydecimal,
t3.fname AS fbuunitname,
Round(t1.fqty,t2.fqtydecimal) AS factbuunitqty,
t2.fsecinv AS fsecinvbuunitqty,
fbuunitdiff = Round(t1.fqty - t2.fsecinv,t2.fqtydecimal),
t4.fname AS fcuunitname,
Round(t1.fqty / t4.fcoefficient,t2.fqtydecimal) AS factcuunitqty,
Round(t2.fsecinv / t4.fcoefficient,t2.fqtydecimal) AS fsecinvcuunitqty,
fcuunitdiff = Round(t1.fqty / t4.fcoefficient - t2.fsecinv / t4.fcoefficient,
t2.fqtydecimal),
fstatus = (CASE
WHEN Round(t1.fqty,t2.fqtydecimal) - t2.fsecinv < 0 THEN '库存不足'
ELSE ''
END),
fsumsort = 0
INTO #temp
FROM #icinventory t1
INNER JOIN t_icitem t2
ON t1.fitemid = t2.fitemid
INNER JOIN t_measureunit t3
ON t2.funitid = t3.fmeasureunitid
INNER JOIN t_measureunit t4
ON t2.fstoreunitid = t4.fmeasureunitid
LEFT JOIN t_item t5
ON t2.fparentid = t5.fitemid
WHERE (t1.fqty / t4.fcoefficient - t2.fsecinv / t4.fcoefficient) < 0
ORDER BY t2.fnumber
IF EXISTS (SELECT *
FROM #temp)
INSERT INTO #temp
SELECT '',
'',
'',
'合计',
'',
4,
'',
Sum(factbuunitqty),
Sum(fsecinvbuunitqty),
Sum(fbuunitdiff),
'',
Sum(factcuunitqty),
Sum(fsecinvcuunitqty),
Sum(fcuunitdiff),
'',
101
FROM #temp
SELECT *
FROM #temp
WHERE fbuunitdiff < 0
ORDER BY fsumsort
DROP TABLE #icinventory
DROP TABLE #temp
推荐阅读