销售交货报表

示例代码

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode]
	, T1.[Dscription], T4.[SlpName] AS 业务员, T1.[UseBaseUn], T1.[Quantity]
	, T1.[Price], T1.[VatPrcnt], T1.[DiscPrcnt], T1.[LineTotal], TS.[Quantity] AS 已开票数量
	, T0.[DocStatus], T0.[TotalExpns], T1.[WhsCode], T1.[OpenQty], T2.[OnHand],T0.[Comments]
FROM ODLN T0
	INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
	INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode]
	INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod]
	INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]
	LEFT JOIN (
		SELECT T1.[Quantity], T1.[BaseEntry], T1.[BaseLine]
		FROM OINV T0
			INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]
	) TS
	ON TS.[BaseEntry] = t0.docentry
		AND Ts.[BaseLine] = T1.[LineNum]
WHERE (T1.[ItemCode] >= '[%0]'
		OR '[%0]' = '')
	AND (T1.[ItemCode] <= '[%1]'
		OR '[%1]' = '')
	AND (T0.[taxDate] >= '[%4]'
		OR '[%4]' = '')
	AND (T0.[taxDate] <= '[%14]'
		OR '[%14]' = '')
	AND (T0.[DocStatus] = '[%5]'
		OR '[%5]' = '')
	AND (T3.[ItmsGrpNam] >= '[%6]'
		OR '[%6]' = '')
	AND (T3.[ItmsGrpNam] <= '[%16]'
		OR '[%16]' = '')
	AND (T4.[slpname] >= '[%7]'
		OR '[%7]' = '')
	AND (T4.[slpname] <= '[%17]'
		OR '[%17]' = '')
	AND (T0.[CARDCODE] >= '[%8]'
		OR '[%8]' = '')
	AND (T0.[CARDCODE] <= '[%18]'
		OR '[%18]' = '')
	AND (T0.[Cardname] >= '[%9]'
		OR '[%9]' = '')
	AND (T0.[Cardname] <= '[%19]'
		OR '[%19]' = '')
	AND (T0.[DOCNUM] >= [%10]
		OR [%10] = '')
	AND (T0.[DOCNUM] <= [%20]
		OR [%20] = '')
	AND T0.[CANCELED] = 'N'
ORDER BY T0.[DocNum] DESC, T1.[LineNum]