销售订单报表

示例代码

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 ORDR T0
	INNER JOIN RDR1 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 ODLN T0
			INNER JOIN DLN1 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]