销售订单交付明细表

示例代码

SELECT T0.[DocNum], T0.[CardName]
, T0.[DocDate], T0.[DocDueDate]
, T1.[ItemCode],  T1.[Dscription]
	, T1.[Quantity] AS '订单数量', Ts.qty AS '已交货数量', TR.RQTY AS '已退货数量'
	, T1.[Quantity] - ISNULL(Ts.qty, 0) - ISNULL(TR.RQTY, 0) AS '未清数量'
	
FROM ORDR T0
	INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
	LEFT JOIN (
		SELECT T0.[BaseEntry], T0.[BaseLine], SUM(T0.[Quantity]) AS 'QTY'
		FROM DLN1 T0
		GROUP BY T0.[BaseEntry], T0.[BaseLine]
	) TS
	ON TS.[BaseEntry] = t1.docentry
		AND TS.[BaseLine] = T1.[LineNum]
	LEFT JOIN (
		SELECT T0.[BaseEntry], T0.[BaseLine], SUM(T1.[Quantity]) AS 'RQTY'
		FROM DLN1 T0
			INNER JOIN RDN1 T1
			ON T1.[BaseEntry] = T0.[DocEntry]
				AND T1.[BaseLine] = T0.[LineNum]
		GROUP BY T0.[BaseEntry], T0.[BaseLine]
	) TR
	ON TR.[BaseEntry] = t1.docentry
		AND TR.[BaseLine] = T1.[LineNum]
WHERE (T0.[taxDate] >= '[%1]'
		OR '[%1]' = '')
	AND (T0.[taxDate] <= '[%2]'
		OR '[%2]' = '')
	AND (T0.[DOCNUM] >= [%3]
		OR [%3] = '')
	AND (T0.[DOCNUM] <= [%4]
		OR [%4] = '')
	AND T0.[CANCELED] = 'N'