销售订单交付明细表
小于 1 分钟
示例代码
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'