--带批次的交易日志
WITH
  batchTrans
  AS
    (SELECT t0.TransSeq, t0.MessageID,
            t0.TransType, t0.BASE_REF AS DocNum,
            t0.DocDate, t0.LocCode AS WhsCode,
            t0.ItemCode, t1.SysNumber,
            t1.MdAbsEntry, CASE WHEN t0.InQty > 0.0 THEN isnull(t1.Quantity, t0.InQty) ELSE 0.0 END AS InQty,
            CASE WHEN t0.OutQty > 0.0 THEN isnull(t1.Quantity, t0.OutQty) ELSE 0.0 END AS OutQty
     FROM   OIVL t0 WITH(NOLOCK) LEFT JOIN ILM1 t1 WITH(NOLOCK) ON t1.MessageID = t0.MessageID)
--带批次的库龄计算
SELECT tt0.DocNum, tt0.TransType, tt0.DocDate, datediff(day, tt0.DocDate, getdate()) AS diffday,
       tt0.WhsCode, tt0.ItemCode, tt0.SysNumber, tt0.MdAbsEntry,
       CASE WHEN tt0.sumQty - tt0.sumOutQty <= tt0.InQty THEN tt0.sumQty - tt0.sumOutQty ELSE tt0.InQty END AS balQty
FROM   (SELECT t0.TransSeq, t0.MessageID, t0.TransType, t0.DocNum, t0.DocDate, t0.WhsCode, t0.ItemCode, t0.SysNumber,
               t0.MdAbsEntry, t0.InQty, t1.sumQty, t2.sumOutQty
        FROM   batchTrans t0
               --获取当前进仓单据日期的结存量
               OUTER APPLY
               (SELECT   sum(isnull(a0.InQty, 0.0) - isnull(a0.OutQty, 0.0)) AS sumQty
                FROM     batchTrans a0
                WHERE    a0.WhsCode = t0.WhsCode AND a0.ItemCode = t0.ItemCode AND isnull(a0.SysNumber,'')=isnull(t0.SysNumber,'')  AND a0.TransSeq <=
                         t0.TransSeq
                GROUP BY a0.WhsCode, a0.ItemCode) t1
               --获取当前进仓单据日期到现在的累计出货量
               OUTER APPLY (SELECT   sum(isnull(a0.OutQty, 0.0)) AS sumOutQty
                            FROM     batchTrans a0
                            WHERE    a0.WhsCode = t0.WhsCode AND a0.ItemCode = t0.ItemCode AND isnull(a0.SysNumber,'')=isnull(t0.SysNumber,'') AND a0.TransSeq >=
                         t0.TransSeq
                            GROUP BY a0.WhsCode, a0.ItemCode) t2
        WHERE  t0.InQty > 0.0 ) tt0
--结存量和累计出货量相减后与当前进仓单据的数量做比较，两者取小，最后排除结果中小于等于0的数据
WHERE  CASE WHEN tt0.sumQty - tt0.sumOutQty <= tt0.InQty THEN tt0.sumQty - tt0.sumOutQty ELSE tt0.InQty END > 0