0

I'm currently struggling with a (at least for me) pretty complex SQL query. I'm trying to calculate commissions.

To calculate properly I need to subtract credits from invoices. As there is no direct relationship between invoices and corresponding credits, I'm joining over their positions.

Anyways, I'd like to display each Invoice (and its creditMemo) only once. DISTINCT however doesn't work here due to the join with the positions (I guess). So how could I achieve a clean list of all invoices including their creditmemos (if exists) only once each?

I know of the GROUP BY clause. I can not manage to use it without losing accurate invoice values due to missing positions...

Any idea how to manage this case?

(removed unnecessary information)

SELECT DISTINCT
R.DocNum as InvoiceNo,
R.DocDate as Date,
R.CardCode as BusinessPartnerID,
R.CardName as BusinessPartnerName,
R.DocTotal as wVat,
(R.DocTotal - R.VatSum) as NoVat,
SUM(R1.LineTotal) OVER () as R_NoVatAll, 
SUM(R1.LineTotal) OVER () + SUM(R1.VatSum) OVER () AS wVatAll, 
R.GrosProfit as Gross,
SUM(R1.GrssProfit) OVER () AS GrossAll, 
R.VatSum as VatSum,

G.DocNum as G_Gutschriftnummer,
G.DocDate as G_Datum,
G.DocTotal as G_wVat,
(G.DocTotal - G.VatSum) as G_NoVat,
SUM(-G1.LineTotal) OVER () AS G_noVatAll,
SUM(-G1.GrssProfit) OVER () AS G_grossAll,
-G.VatSum as G_Ust,

V.slpName as slpName, 
V.Commission as Commission,
(ISNULL(V.Commission,8)/100*R1.GrssProfit) as CommissionValue, (ISNULL(V.Commission,8)/100*-(G1.GrssProfit + G1.VatSum) ) as CommissionValueMinus,
(SUM(-(G1.LineTotal )) OVER()) as CreditAll, 
(SUM(R1.GrssProfit) OVER () - SUM(G1.GrssProfit) OVER ()) as CommissionBase,
(SUM(R1.GrssProfit) OVER () - SUM(G1.GrssProfit) OVER ()) * 8/100 as CommissionTotal

FROM OINV R -- Invoices
INNER JOIN INV1 R1 ON R.DocEntry = R1.DocEntry -- InvoicePositions
LEFT JOIN RIN1 G1 ON G1.BaseEntry = R1.DocEntry AND G1.BaseLine = R1.LineNum     AND G1.BaseType = 13 -- CreditPositions
FULL JOIN ORIN G ON G1.DocEntry = G.DocEntry -- Credits
LEFT JOIN OSLP V ON G.slpCode = V.slpCode -- SLPs

WHERE 1=1
AND (R.DocDate BETWEEN '07.01.2015' AND '07.31.2015' OR G.DocDate BETWEEN '07.01.2015' AND '07.31.2015') 
AND R1.AcctCode <> '9085' 
AND R.DocTotal <> 0 
AND R.CANCELED <> 'Y'
AND G.DocTotal <> 0     

ORDER BY R.DocNum
vwegert
  • 18,371
  • 3
  • 37
  • 55
Akaino
  • 1,025
  • 6
  • 23
  • Find the field that's causing the duplication, decide on the logic for which record's field you want to display, and update the query to do that. There isn't enough information about what your results look like to be more specific. – Bacon Bits Aug 06 '15 at 14:23
  • possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – Bulat Aug 06 '15 at 14:24
  • Also note that the following WHERE clause conditions are turning most of your OUTER joins into implicit INNER joins: `AND R1.AcctCode <> '9085' AND R.DocTotal <> 0 AND R.CANCELED <> 'Y' AND G.DocTotal <> 0`. Your WHERE clause would need to allow for these values to be NULL if you truly want OUTER joins. – Bacon Bits Aug 06 '15 at 14:25
  • The OUTER join problem is ok so far. Those AND clauses are just for testing. The fields causing the problem seem to be the different positions of the invoices. I need them summed up but don't know how to achieve that without losing information. What else do you need? An example list maybe? – Akaino Aug 06 '15 at 14:26

0 Answers0