库龄分析报表（带金额）
大约 5 分钟
示例代码
DECLARE @DATE DATETIME
SELECT @DATE = T.CREATEDATE FROM OIVL T WHERE T.CREATEDATE = '[%0]'
IF(ISNULL(@DATE,'')='')
BEGIN
SET @DATE=CONVERT(NVARCHAR(10),GETDATE())
END
SELECT
T0.ITEMCODE,
ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE>@DATE
GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY,
ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<=@DATE AND CREATEDATE>=@DATE-30
GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY30,
ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-30 AND CREATEDATE>=@DATE-60
GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY60,
ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-60 AND CREATEDATE>=@DATE-90
GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY90,
ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-90 AND CREATEDATE>=@DATE-180
GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY180,
ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-180 AND CREATEDATE>=@DATE-360
GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY360,
ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-360 AND CREATEDATE>=@DATE-540
GROUP BY ITEMCODE,LOCCODE),0) AS BEGQTY540,
ISNULL((SELECT SUM(INQTY) FROM OIVL WHERE ITEMCODE=T0.ITEMCODE AND LOCCODE=T0.LOCCODE AND CREATEDATE<@DATE-540
GROUP BY ITEMCODE,LOCCODE),0) AS ENDQTY,
T0.LOCCODE,T2.ONHAND
INTO #TEMP_1
FROM OITM T1 INNER JOIN OIVL T0 ON T1.ITEMCODE = T0.ITEMCODE
INNER JOIN OITW T2 ON T2.ITEMCODE = T1.ITEMCODE AND T2.WHSCODE = T0.LOCCODE
WHERE T1.FROZENFOR = 'N' AND T1.MANSERNUM ='N' AND T1.MANBTCHNUM ='N'
AND T2.ONHAND <> 0
GROUP BY T0.ITEMCODE,T0.LOCCODE,T2.ONHAND
SELECT ITEMCODE,
CASE WHEN (ONHAND-BEGQTY)<=0 THEN ONHAND
ELSE BEGQTY END AS BEGQTY,
CASE WHEN (ONHAND-BEGQTY)<=0 THEN 0
ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30)<=0 THEN (ONHAND-BEGQTY)
ELSE BEGQTY30 END END AS BEGQTY30,
CASE WHEN (ONHAND-BEGQTY-BEGQTY30)<=0 THEN 0
ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60)<=0 THEN (ONHAND-BEGQTY-BEGQTY30)
ELSE BEGQTY60 END END AS BEGQTY60,
CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60)<=0 THEN 0
ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90)<=0 THEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60)
ELSE BEGQTY90 END END AS BEGQTY90,
CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90)<=0 THEN 0
ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180)<=0 THEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90)
ELSE BEGQTY180 END END AS BEGQTY180,
CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180)<=0 THEN 0
ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360)<=0 THEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180)
ELSE BEGQTY360 END END AS BEGQTY360,
CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360)<=0 THEN 0
ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360-BEGQTY540)<=0 THEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360)
ELSE BEGQTY540 END END AS BEGQTY540,
CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360-BEGQTY540)<=0 THEN 0
ELSE CASE WHEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360-BEGQTY540-ENDQTY)<=0 THEN (ONHAND-BEGQTY-BEGQTY30-BEGQTY60-BEGQTY90-BEGQTY180-BEGQTY360-BEGQTY540)
ELSE ENDQTY END END AS ENDQTY,
LOCCODE,ONHAND
INTO #TEMP_2
FROM #TEMP_1
SELECT N'无序列号无批次管理' AS '管理方式',T0.ITEMCODE AS '物料编号',T2.ITEMNAME AS '物料描述',T2.FRGNNAME AS '规格型号',
T0.BEGQTY30 AS '0-30',
T4.AVGPRICE*T0.BEGQTY30 AS '0-30(金额)',
T0.BEGQTY60 AS '31-60',
T4.AVGPRICE*T0.BEGQTY60 AS '31-60(金额)',
T0.BEGQTY90 AS '61-90',
T4.AVGPRICE*T0.BEGQTY90 AS '61-90(金额)',
T0.BEGQTY180 AS '91-180',
T4.AVGPRICE*T0.BEGQTY180 AS '91-180(金额)',
T0.BEGQTY360 AS '181-360',
T4.AVGPRICE*T0.BEGQTY360 AS '181-360(金额)',
T0.BEGQTY540 AS '361-540',
T4.AVGPRICE*T0.BEGQTY540 AS '361-540(金额)',
T0.ENDQTY AS '541++',
T4.AVGPRICE*T0.ENDQTY AS '541++(金额)',
T1.WHSNAME AS '仓库',
T0.ONHAND AS '现有库存',
T0.ONHAND*T4.AVGPRICE AS '现有库存金额'
FROM #TEMP_2 T0 INNER JOIN OWHS T1 ON T0.LOCCODE = T1.WHSCODE
INNER JOIN OITM T2 ON T2.ITEMCODE = T0.ITEMCODE
INNER JOIN OITW T4 ON T4.ITEMCODE = T0.ITEMCODE AND T4.WHSCODE = T0.LOCCODE
UNION ALL
SELECT N'序列号管理' AS '管理方式',T0.ITEMCODE AS '物料编号',T2.ITEMNAME AS '物料描述',T2.FRGNNAME AS '规格型号',
ISNULL(CASE WHEN T0.INDATE>=@DATE-30 THEN T0.QTY END,0) AS '0-30',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-30 THEN T0.QTY END,0) AS '0-30(金额)',
ISNULL(CASE WHEN T0.INDATE>=@DATE-60 AND T0.INDATE<@DATE-30 THEN T0.QTY END,0) AS '31-60',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-60 AND T0.INDATE<@DATE-30 THEN T0.QTY END,0) AS '31-60(金额)',
ISNULL(CASE WHEN T0.INDATE>=@DATE-90 AND T0.INDATE<@DATE-60 THEN T0.QTY END,0) AS '61-90',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-90 AND T0.INDATE<@DATE-60 THEN T0.QTY END,0) AS '61-90(金额)',
ISNULL(CASE WHEN T0.INDATE>=@DATE-180 AND T0.INDATE<@DATE-90 THEN T0.QTY END,0) AS '91-180',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-180 AND T0.INDATE<@DATE-90 THEN T0.QTY END,0) AS '91-180(金额)',
ISNULL(CASE WHEN T0.INDATE>=@DATE-360 AND T0.INDATE<@DATE-180 THEN T0.QTY END,0) AS '181-360',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-360 AND T0.INDATE<@DATE-180 THEN T0.QTY END,0) AS '181-360(金额)',
ISNULL(CASE WHEN T0.INDATE>=@DATE-540 AND T0.INDATE<@DATE-360 THEN T0.QTY END,0) AS '361-540',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-540 AND T0.INDATE<@DATE-360 THEN T0.QTY END,0) AS '361-540(金额)',
ISNULL(CASE WHEN T0.INDATE<@DATE-540 THEN T0.QTY END,0) AS '541++',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE<@DATE-540 THEN T0.QTY END,0) AS '541++(金额)',
T1.WHSNAME AS '仓库',
T4.ONHAND AS '现有库存',
T4.ONHAND*T4.AVGPRICE AS '现有库存金额'
FROM (
SELECT T0.ITEMCODE,T0.WHSCODE,T1.INDATE,COUNT(*) AS QTY FROM OSRQ T0
INNER JOIN OSRN T1 ON T1.ABSENTRY=T0.ABSENTRY
WHERE ISNULL(T0.QUANTITY,0)>0
GROUP BY T0.ITEMCODE,T0.WHSCODE,T1.INDATE
) T0 INNER JOIN OWHS T1 ON T0.WHSCODE = T1.WHSCODE
INNER JOIN OITM T2 ON T2.ITEMCODE = T0.ITEMCODE
INNER JOIN OITW T4 ON T4.ITEMCODE = T0.ITEMCODE AND T4.WHSCODE = T0.WHSCODE
UNION ALL
SELECT N'批次管理' AS '管理方式',T0.ITEMCODE AS '物料编号',T2.ITEMNAME AS '物料描述',T2.FRGNNAME AS '规格型号',
ISNULL(CASE WHEN T0.INDATE>=@DATE-30 THEN T0.QTY END,0) AS '0-30',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-30 THEN T0.QTY END,0) AS '0-30(金额)',
ISNULL(CASE WHEN T0.INDATE>=@DATE-60 AND T0.INDATE<@DATE-30 THEN T0.QTY END,0) AS '31-60',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-60 AND T0.INDATE<@DATE-30 THEN T0.QTY END,0) AS '31-60(金额)',
ISNULL(CASE WHEN T0.INDATE>=@DATE-90 AND T0.INDATE<@DATE-60 THEN T0.QTY END,0) AS '61-90',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-90 AND T0.INDATE<@DATE-60 THEN T0.QTY END,0) AS '61-90(金额)',
ISNULL(CASE WHEN T0.INDATE>=@DATE-180 AND T0.INDATE<@DATE-90 THEN T0.QTY END,0) AS '91-180',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-180 AND T0.INDATE<@DATE-90 THEN T0.QTY END,0) AS '91-180(金额)',
ISNULL(CASE WHEN T0.INDATE>=@DATE-360 AND T0.INDATE<@DATE-180 THEN T0.QTY END,0) AS '181-360',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-360 AND T0.INDATE<@DATE-180 THEN T0.QTY END,0) AS '181-360(金额)',
ISNULL(CASE WHEN T0.INDATE>=@DATE-540 AND T0.INDATE<@DATE-360 THEN T0.QTY END,0) AS '361-540',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE>=@DATE-540 AND T0.INDATE<@DATE-360 THEN T0.QTY END,0) AS '361-540(金额)',
ISNULL(CASE WHEN T0.INDATE<@DATE-540 THEN T0.QTY END,0) AS '541++',
T4.AVGPRICE*ISNULL(CASE WHEN T0.INDATE<@DATE-540 THEN T0.QTY END,0) AS '541++(金额)',
T1.WHSNAME AS '仓库',
T4.ONHAND AS '现有库存',
T4.ONHAND*T4.AVGPRICE AS '现有库存金额'
FROM (
SELECT T0.ITEMCODE,T0.WHSCODE,T1.INDATE,SUM(ISNULL(T0.QUANTITY,0)) AS QTY FROM OBTQ T0
INNER JOIN OBTN T1 ON T1.ABSENTRY=T0.ABSENTRY
WHERE ISNULL(T0.QUANTITY,0)>0
GROUP BY T0.ITEMCODE,T0.WHSCODE,T1.INDATE
) T0 INNER JOIN OWHS T1 ON T0.WHSCODE = T1.WHSCODE
INNER JOIN OITM T2 ON T2.ITEMCODE = T0.ITEMCODE
INNER JOIN OITW T4 ON T4.ITEMCODE = T0.ITEMCODE AND T4.WHSCODE = T0.WHSCODE
ORDER BY T0.ITEMCODE
DROP TABLE #TEMP_1
DROP TABLE #TEMP_2