Query on Journal Tables to show Net Sales

[Former Member]

Hi all

Would like some advice on how to write a customised sales analysis query showing the Net Sales using the journal tables.

The beginning of my query is as below:

SELECT T2.[SlpName], T1.[CardCode], T1.[CardName], T0.[U_01] AS 'January Target',

SUM(CASE WHEN month(T3.[RefDate]) = '1' THEN T3.[Debit] - T3.[Credit] END) AS 'January Total'

FROM [dbo].[@SALES_TARGETS]  T0

INNER JOIN [dbo].[OCRD] T1 ON T0.[Code] = T1.[CardCode]

INNER JOIN [dbo].[OSLP] T2 ON T1.[SlpCode] = T2.[SlpCode]

INNER JOIN [dbo].[JDT1] T3 ON T1.[CardCode] = T3.[ShortName]

INNER JOIN [dbo].[OJDT] T4 ON T3.[TransId] = T4.[TransId]

WHERE year(T3.[RefDate]) = '2015' AND T3.[TransType] IN ('13','14')

GROUP BY T2.[SlpName], T1.[CardCode], T1.[CardName], T0.[U_01]

This executes correctly, however the total column (debit - credit) is giving the gross total whereas I need the nett - I need to remove the tax amounts.

I can't see any obvious way of doing this - would welcome any assistance.

Many thanks

wendy

[KennedyT21 · Accepted solution]

Hi Wendy ..

You can go with the sales invoice and credit memo table instead of  OJDT table...

Regards,

Kennedy

[Former Member · Accepted solution]

Hello

Thanks, I will do this. I was under the impression that it would be more accurate to use the OJDT table due to differences in dates, but I can build a query from the OINV table and see how it goes.