Sales Analysis query

[Former Member]

Hi Experts,

I have a client who needs the Sales Analysis report modified in SBO. They need this in a query to export to Excel.

I have difficulty getting the Sales Total per Customer per month. If I get help on getting the result for one month I can add the rest. This will have to include Invoices - Credit Memos, or the debit - the credit in the Jornal entries.

Here is what I have done so far:

SELECT Distinct T0.[CardCode], T0.[CardName],
 
(SELECT (SUM(T2.Debit) - sum(T2.Credit))
FROM  OCRD T0 INNER JOIN JDT1 T1 ON T1.ShortName = T0.CardCode 
WHERE T0.CardType = 'C' AND DateDiff(DD,T2.Duedate,GetDate()) between 0 and 30) AS 'present month',

FROM OCRD T0 
INNER JOIN JDT1 T1 ON T1.ShortName = T0.CardCode
WHERE T0.CardType = 'C'

Any help would be appreciated.

Marli

[Former Member · Accepted solution]

Hi Marli,

Try this:

SELECT T0.[CardCode], T0.[CardName], (SUM(T1.Debit) - sum(T1.Credit)) AS '0 - 30'
FROM  OCRD T0 INNER JOIN JDT1 T1 ON T1.ShortName = T0.CardCode 
WHERE T0.CardType = 'C' AND DateDiff(DD,T1.Duedate,GetDate()) between 0 and 30
Group By  T0.[CardCode], T0.[CardName]

Thanks,

Gordon

[Former Member · Accepted solution]

Gordon,

Thanks. This query works if you want to display the 0-30 days aging.

What I'm after is to display the balance in a specific month. Ideally I need to display this for July and August of this year seperately.

Your help is much appreciated.

Marli

[Former Member · Accepted solution]

Try this:

SELECT T0.[CardCode], T0.[CardName], (SUM(T1.Debit) - sum(T1.Credit)) AS 'July',
 (SUM(T2.Debit) - sum(T2.Credit)) AS 'August'
FROM  OCRD T0 
LEFT JOIN JDT1 T1 ON T1.ShortName = T0.CardCode 
LEFT JOIN JDT1 T2 ON T2.ShortName = T0.CardCode 
WHERE T0.CardType = 'C' AND Month(T1.Duedate) = 7 AND Year(T1.Duedate) = 2010 
AND Month(T2.Duedate) = 8 AND Year(T2.Duedate) = 2010
Group By T0.[CardCode], T0.[CardName]

[Former Member · Accepted solution]

Gordon,

Thanks.

This query only picks up Customers that had Invoices for both July and August. I need this to pick up all sales for those months.

It also picks up Payments. Is there a way we can show only the Sales - not the payment?

Regards,

Marli

[Former Member · Accepted solution]

Try this:

SELECT T0.[CardCode], T0.[CardName], (SUM(T1.Debit) - sum(T1.Credit)) AS 'July',
 (SUM(T2.Debit) - sum(T2.Credit)) AS 'August'
FROM  OCRD T0 
LEFT JOIN JDT1 T1 ON T1.ShortName = T0.CardCode AND Month(T1.Duedate) = 7  AND Year(T1.Duedate) = 2010 AND T1.TransType
in ('13','14')
LEFT JOIN JDT1 T2 ON T2.ShortName = T0.CardCode AND Month(T2.Duedate) = 8 AND Year(T2.Duedate) = 2010 AND T2.TransType
in ('13','14')
WHERE T0.CardType = 'C' 
Group By T0.[CardCode], T0.[CardName]