|
软件环境：SAP Business One 9.2
首先感谢ITPUB论坛的“guozhiqiang01”提供源码和思路，在基础上对SAP Business One 进销存明细报表的查询代码优化如下：
SELECT t2.TransNum 交易编号,
t2.TransType 交易类型,
t1.ItemCode 物料编号,
t1.ItemName 物料名称,
t2.DocDate 交易日期,
(
SELECT SUM(t3.InQty) -SUM(t3.OutQty)
FROM OINM t3
WHERE t3.ItemCode = t2.ItemCode
AND t3.TransNum < t2.TransNum
) 期初数量,
t2.InQty 收货数量,
t2.OutQty 发货数量,
(
SELECT SUM(t3.InQty) -SUM(t3.OutQty)
FROM OINM t3
WHERE t3.ItemCode = t2.ItemCode
AND t3.TransNum <= t2.TransNum
) 期末数量
FROM OITM t1
LEFT JOIN OINM t2
ON t1.ItemCode = t2.ItemCode
WHERE t2.TransNum IS NOT NULL AND t2.DocDate = '2018.08.01'
ORDER BY
t1.ItemCode,
t2.TransNum
GROUP BY
t2.DocDate,
t1.ItemCode
欢迎测试，改进，谢谢！ | 
转播
