Sales total in a given time by account.

[jbrotto]

I wrote a query to show the invoices total amounts by customer and some accounts are consolidated by a head account. I get alot of nulls for names and also I'll like if I could combine the credit table as well.

SELECT CASE T0.[FatherCard]

WHEN NULL THEN T1.[CardName]

WHEN '' THEN T1.[CardName]

ELSE T0.[FatherCard]

END AS 'Name', SUM(T1.[DocTotal]) AS 'INVOICE TOTAL', SUM( T1.[VatSum]) AS 'TAX TOTAL', SUM( T1.[DocTotal] - T1.[VatSum]) AS 'PRE TAX TOTAL'

FROM OCRD T0, OINV T1

WHERE T1.[DocDate] >=[%0] AND T1.[DocDate] <=[%1] AND T0.CardCode = T1.CardCode

GROUP BY T0.[CardName], T0.[FatherCard], T1.[CardName])

GROUP BY 'NAME'

[Former Member · Accepted solution]

Hi,

First check what is the result of this:

SELECT CASE T0.FatherCard

WHEN NULL THEN T1.CardName

WHEN '' THEN T1.CardName

ELSE T0.FatherCard

END AS 'Name', SUM(T1.DocTotal) AS 'INVOICE TOTAL', SUM( T1.VatSum) AS 'TAX TOTAL', SUM( T1.DocTotal - T1.VatSum) AS 'PRE TAX TOTAL'

FROM OCRD T0

INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode

WHERE T1.DocDate >=[%0\] AND T1.DocDate <=[%1\]

GROUP BY T0.FatherCard, T1.CardName

Thanks,

Gordon

[jbrotto · Accepted solution]

Thanks but I got the same results with the name colunm having a bunch of null values. Tried in query manager and sql server. If anythig a few values might be in a deifferent order but the same sum for the amount colunms. I thought doing a nested queries but SAP query generator is very fussy. Do you have a solution in your book for this kind of problem or could you provide a sample solution to remove the null values. Also my persnal prefence is a natural join but I know in most cases either a left or inner join is better.

[Former Member · Accepted solution]

If I have similar data in our database, I am sure I can solve your problem. However, we don't use consolidated BP. Nothing I can test with.