采购收货查询当月未开应付发票数量

提示

以采购收货单数量为核心，统计采购收货单当月的应付发票数量，采购收货数量-当月应付发票数量=当月采购收货单未开票数量

1.单据日期取年份和月份 2. 计算公式如存在空白的用 ISNULL(X,0) 标示

代码示例

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocDueDate], T1.[ItemCode]
	, T1.[Dscription], T1.[Quantity]
	, T1.[Quantity] - ISNULL(TS.Qty, 0) AS '收货当月未开票数量'
FROM OPDN T0
	INNER JOIN PDN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
	LEFT JOIN (
		SELECT T0.[ItemCode], SUM(T0.[Quantity]) AS 'Qty', T0.[BaseEntry], T0.[BaseLine]
			, convert(varchar(7), T0.[ShipDate], 121) AS 'NY'
		FROM PCH1 T0
		GROUP BY T0.[ItemCode], T0.[BaseEntry], T0.[BaseLine], T0.[ShipDate]
	) TS
	ON Ts.[BaseLine] = T1.[BaseLine]
		AND TS.[BaseEntry] = T0.[DocEntry]
		AND TS.[ItemCode] = T1.[ItemCode]
		AND TS.NY = convert(varchar(7), T0.[DocDate], 121)
ORDER BY T0.[DocNum] DESC