库存物料呆滞天数查询

提示

--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]

效果图

仓储