账龄

示例代码

SELECT m0.CntUnitMsr, m0.ItmsGrpNam, m0.ItemCode, m0.ItemName, m1.qu AS '半年内数量'
	, m1.qu * n0.price AS '金额', m2.qu AS '半年到一年数量'
	, m2.qu * n0.price AS '金额', m3.qu AS '一年到两年数量'
	, m3.qu * n0.price AS '金额', m4.qu AS '两年以上数量'
	, m4.qu * n0.price AS '金额', m0.qu AS '合计数量'
	, m0.qu * n0.price AS '总金额'
FROM (
	SELECT t0.ItemCode, t1.itemname, SUM(t0.Quantity) AS qu, t1.CntUnitMsr
		, t2.ItmsGrpNam
	FROM OIBT t0
		LEFT JOIN OITM t1 ON t0.ItemCode = t1.ItemCode
		LEFT JOIN OITB t2 ON t1.ItmsGrpCod = t2.ItmsGrpCod
	WHERE t0.Quantity > 0
	GROUP BY t0.ItemCode, t1.ItemName, t1.CntUnitMsr, t2.ItmsGrpNam
) m0
	LEFT JOIN (
		SELECT t0.ItemCode, SUM(t0.Quantity) AS qu
		FROM OIBT t0
			LEFT JOIN OITM t1 ON t0.ItemCode = t1.ItemCode
		WHERE t0.Quantity > 0
			AND DATEDIFF(MM, t0.InDate, GETDATE()) <= '6'
		GROUP BY t0.ItemCode
	) m1
	ON m0.ItemCode = m1.ItemCode
	LEFT JOIN (
		SELECT t0.ItemCode, SUM(t0.Quantity) AS qu
		FROM OIBT t0
			LEFT JOIN OITM t1 ON t0.ItemCode = t1.ItemCode
		WHERE t0.Quantity > 0
			AND DATEDIFF(MM, t0.InDate, GETDATE()) > '6'
			AND DATEDIFF(MM, t0.InDate, GETDATE()) <= '12'
		GROUP BY t0.ItemCode
	) m2
	ON m0.ItemCode = m2.ItemCode
	LEFT JOIN (
		SELECT t0.ItemCode, SUM(t0.Quantity) AS qu
		FROM OIBT t0
			LEFT JOIN OITM t1 ON t0.ItemCode = t1.ItemCode
		WHERE t0.Quantity > 0
			AND DATEDIFF(MM, t0.InDate, GETDATE()) > '12'
			AND DATEDIFF(MM, t0.InDate, GETDATE()) <= '24'
		GROUP BY t0.ItemCode
	) m3
	ON m0.ItemCode = m3.ItemCode
	LEFT JOIN (
		SELECT t0.ItemCode, SUM(t0.Quantity) AS qu
		FROM OIBT t0
			LEFT JOIN OITM t1 ON t0.ItemCode = t1.ItemCode
		WHERE t0.Quantity > 0
			AND DATEDIFF(MM, t0.InDate, GETDATE()) > '24'
		GROUP BY t0.ItemCode
	) m4
	ON m0.ItemCode = m4.ItemCode
	LEFT JOIN (
		SELECT t0.ItemCode, SUM(t0.StockValue) / SUM(t0.OnHand) AS price
		FROM OITW t0
		WHERE t0.OnHand > 0
		GROUP BY t0.ItemCode
	) n0
	ON m0.ItemCode = n0.ItemCode
ORDER BY m0.ItemCode