销售订单毛利表

代码示例

SELECT T3.[GroupName], T4.[SlpName] AS '业务员', T0.[DocNum], T0.[DocDate],T0.[NumAtCard] AS '合同订单号', T1.[ItemCode]
	, T1.[Dscription], Ta.[Docentry] AS '交货单号', Ta.[DocDate] AS '交货日期', Ta.[Quantity] AS '出货数量', Ta.[PriceAfVAT] AS '含税单价'
	, TA.[GTotal] AS '交货含税总计', Ta.[Price] AS '未税单价', Ta.[LineTotal] AS '未税金额', Ta.[VatPrcnt] AS '税率', Ta.[VatSum]
	, Ta.[StockPrice], ta.zcb AS '总成本',  Ta.dml AS '单毛利', Ta.[GrssProfit] AS '总毛利',ta.mll AS '毛利率 % '
FROM ORDR T0
	INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
	INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]
	INNER JOIN OCRG T3 ON T2.[GroupCode] = T3.[GroupCode]
	INNER JOIN OSLP T4 ON T0.[SlpCode] = T4.[SlpCode]
	LEFT JOIN (
		SELECT T1.[BaseLine], T1.[BaseEntry], T1.[ItemCode], T0.[Docentry], T0.[DocDate]
			, T1.[Quantity], T1.[PriceAfVAT], T1.[GTotal], T1.[Price], T1.[LineTotal]
			, T1.[VatPrcnt], T1.[VatSum], T1.[StockPrice], T1.[StockPrice] * T1.[Quantity] AS 'zcb'
			, T1.[GrssProfit],T1.[GrssProfit]/  T1.[Quantity] as'dml'
			, CASE 
				WHEN T1.[GrossBuyPr] = 0 THEN 100
				WHEN T1.[GrossBuyPr] <> 0 THEN  (T1.[GrssProfit]/  T1.[Quantity])/T1.[GrossBuyPr]*100
			END AS 'mll'
		FROM ODLN T0
			INNER JOIN DLN1 T1 ON T0.[DocEntry] = T1.[DocEntry]
	) TA
	ON Ta.[BaseEntry] = t1.docentry
		AND ta.itemcode = t1.itemcode
		AND Ta.BaseLine = t1.linenum
WHERE T0.[DocDate] >='[%0]'
	AND T0.[DocDate] <= '[%1]'
ORDER BY T0.[DocNum] DESC