Skip to Content
0
Oct 16, 2011 at 01:43 PM

Sales total in a given time by account.

38 Views

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'