求助这个sql server 如何写

ok606

cbatch 210106007 出库数量 20000    4月 开票数   16000   未开 4000

                            如何做到 5月开票    2000 时 4月未开的变成  4000

现在 我这样写的 变成2000了，

SELECT   w.cBatch, w.cInvCode AS 物料号, w.cCode, e.cInvName AS 存货名称, e.cInvStd AS 规格型号,
                c.cCusName AS 客户名称, w.dDate AS 出库日期, u.cComUnitName AS 单位, w.iQuantity AS 出库数量,
                w.iUnitCost AS 单价, ISNULL(w.iQuantity - ISNULL(v.iQuantity, 0), 0) AS 未开数量,
                w.iUnitCost * ISNULL(w.iQuantity - ISNULL(v.iQuantity, 0), 0) AS 未开金额, f.dDate AS 开票日期, ISNULL(d.iQuantity,
                0) AS 已开票数量, d.iUnitPrice, d.iMoney AS 无税金额, d.iSum AS 价税合计
FROM      (SELECT   a.cCode, b.cInvCode, a.dDate, b.iQuantity, b.iNum, b.cBatch, a.cCusCode, b.iUnitCost
                 FROM      dbo.rdrecord32 AS a INNER JOIN
                                 dbo.rdrecords32 AS b ON a.ID = b.ID
                 WHERE   (YEAR(a.dDate) > 2019)) AS w INNER JOIN
                dbo.Customer AS c ON w.cCusCode = c.cCusCode INNER JOIN
                dbo.inventory AS e ON e.cInvCode = w.cInvCode LEFT OUTER JOIN
                dbo.ComputationUnit AS u ON e.cComUnitCode = u.cComunitCode LEFT OUTER JOIN
                dbo.SaleBillVouchs AS d ON d.cbsaleout = w.cCode AND w.cBatch = d.cBatch AND
                w.cInvCode = d.cInvCode LEFT OUTER JOIN
                dbo.SaleBillVouch AS f ON d.SBVID = f.SBVID LEFT OUTER JOIN
                    (SELECT   cbsaleout, cInvCode, cBatch, SUM(iQuantity) AS iQuantity, SUM(iMoney) AS iMoney
                     FROM      dbo.SaleBillVouchs
                     GROUP BY cbsaleout, cInvCode, cBatch) AS v ON v.cbsaleout = w.cCode AND w.cBatch = v.cBatch AND
                w.cInvCode = v.cInvCode


WHERE   (w.cCode = '202203140020')

ntmino

计算未开数量时加个case when条件

cderpll

按期间查询，4月未开票量本来就是4000， 计算如此，不存在，你5月开票了，四月现存量就变了逻辑，，后台数据库要控制期间的

ok606

ntmino 发表于 2022-5-20 11:09
计算未开数量时加个case when条件

这个case when 怎么写

laodacheng

加油一起学习

kaikai7902

复杂的sql