库存物料呆滞天数查询
小于 1 分钟
提示
--1. 取有库存物料最后一次出库日期为初始日期计算呆滞天数 --2. 通过报表内的再次过滤筛选出需要查看的呆滞天数 --3. 若物料未有任何出库记录,则按最后一次入库日期记呆滞初始日期
代码示例
SELECT T0.[ItemCode], T0.[ItemName], T1.[ItmsGrpNam], T0.[OnHand], TC.CS AS '最初入库日期'
, TB.RK AS '最后入库日期', TA.out AS '最后出库日期'
,
CASE WHEN TA.out IS NULL THEN datediff(d, TB.RK, GetDate())
WHEN TA.out IS NOT NULL THEN datediff(d, TA.out, GetDate())
END AS '已呆滞天数'
FROM OITM T0
LEFT JOIN (
SELECT T0.[ItemCode], MAX(T0.[DocDate]) AS 'OUT'
FROM OIVL T0
WHERE T0.[TransType] <> '67'
AND T0.[OutQty] > 0
GROUP BY T0.[ItemCode]
) TA
ON TA.itemcode = t0.itemcode
LEFT JOIN (
SELECT T0.[ItemCode], MAX(T0.[DocDate]) AS 'RK'
FROM OIVL T0
WHERE T0.[TransType] <> '67'
AND T0.[InQty] > 0
GROUP BY T0.[ItemCode]
) TB
ON TB.itemcode = t0.itemcode
LEFT JOIN (
SELECT T0.[ItemCode], MIN(T0.[DocDate]) AS 'CS'
FROM OIVL T0
WHERE T0.[TransType] <> '67'
AND T0.[InQty] > 0
GROUP BY T0.[ItemCode]
) TC
ON TC.itemcode = t0.itemcode
INNER JOIN OITB T1 ON T0.[ItmsGrpCod] = T1.[ItmsGrpCod]
WHERE T0.[OnHand] > 0
ORDER BY T0.[ItemCode]
效果图
![仓储](/assets/18-m7_yAKit.png)