Skip to Content
0
Former Member
Aug 25, 2010 at 02:29 AM

Sales query with no results in last columns

10 Views

Experts,

I have put together the following query with some help in other posts. This must show the Customers Average sales for 2009 and 2010. It must also then show the monthly sales for this year by month. It is this monthly sales that I have issues with. Invoices posted in March, April and August (mabey more) are duplicated.

-


SELECT T0.CardCode, T0.CardName,
(SUM(T1.Debit) - sum(T1.Credit)) AS '2009', ((SUM(T1.Debit) - sum(T1.Credit))/12) AS '2009 Avg', (SUM(T2.Debit) - sum(T2.Credit)) AS '2010', 
 ((SUM(T2.Debit) - sum(T2.Credit))/Month(GetDate())) AS '2010 Avg', 
(SUM(T3.Debit) - sum(T3.Credit)) AS 'January',
(SUM(T4.Debit) - sum(T4.Credit)) AS 'February',
(SUM(T5.Debit) - sum(T5.Credit)) AS 'March',
(SUM(T6.Debit) - sum(T6.Credit)) AS 'April',
(SUM(T7.Debit) - sum(T7.Credit)) AS 'May',
(SUM(T8.Debit) - sum(T8.Credit)) AS 'June',
(SUM(T9.Debit) - sum(T9.Credit)) AS 'July',
(SUM(T10.Debit) - sum(T10.Credit)) AS 'August',
(SUM(T11.Debit) - sum(T11.Credit)) AS 'September',
(SUM(T12.Debit) - sum(T12.Credit)) AS 'October',
(SUM(T13.Debit) - sum(T13.Credit)) AS 'November',
(SUM(T14.Debit) - sum(T14.Credit)) AS 'December'
FROM  OCRD T0 
LEFT JOIN JDT1 T1 ON T1.ShortName = T0.CardCode AND Year(T1.Duedate) = 2009 AND T1.TransType in ('13','14')
LEFT JOIN JDT1 T2 ON T2.ShortName = T0.CardCode AND Year(T2.Duedate) = 2010 AND T2.TransType in ('13','14')
LEFT JOIN JDT1 T3 ON T3.ShortName = T0.CardCode AND Month(T3.Duedate) = 1  AND Year(T3.Duedate) = 2010 AND T3.TransType in ('13','14') 
LEFT JOIN JDT1 T4 ON T4.ShortName = T0.CardCode AND Month(T4.Duedate) = 2 AND Year(T4.Duedate) = 2010 AND T4.TransType in ('13','14') 
LEFT JOIN JDT1 T5 ON T5.ShortName = T0.CardCode AND Month(T5.Duedate) = 3 AND Year(T5.Duedate) = 2010 AND T5.TransType in ('13','14') 
LEFT JOIN JDT1 T6 ON T6.ShortName = T0.CardCode AND Month(T6.Duedate) = 4 AND Year(T6.Duedate) = 2010 AND T6.TransType in ('13','14') 
LEFT JOIN JDT1 T7 ON T7.ShortName = T0.CardCode AND Month(T7.Duedate) = 5 AND Year(T7.Duedate) = 2010 AND T7.TransType in ('13','14') 
LEFT JOIN JDT1 T8 ON T8.ShortName = T0.CardCode AND Month(T8.Duedate) = 6 AND Year(T8.Duedate) = 2010 AND T8.TransType in ('13','14')
LEFT JOIN JDT1 T9 ON T9.ShortName = T0.CardCode AND Month(T9.Duedate) = 7 AND Year(T9.Duedate) = 2010 AND T9.TransType in ('13','14')  
LEFT JOIN JDT1 T10 ON T10.ShortName = T0.CardCode AND Month(T10.Duedate) = 8 AND Year(T10.Duedate) = 2010 AND T10.TransType in ('13','14')
LEFT JOIN JDT1 T11 ON T11.ShortName = T0.CardCode AND Month(T11.Duedate) = 9 AND Year(T11.Duedate) = 2010 AND T11.TransType in ('13','14')  
LEFT JOIN JDT1 T12 ON T12.ShortName = T0.CardCode AND Month(T12.Duedate) = 10 AND Year(T12.Duedate) = 2010 AND T12.TransType in ('13','14') 
LEFT JOIN JDT1 T13 ON T13.ShortName = T0.CardCode AND Month(T13.Duedate) = 11 AND Year(T13.Duedate) = 2010 AND T13.TransType in ('13','14') 
LEFT JOIN JDT1 T14 ON T14.ShortName = T0.CardCode AND Month(T14.Duedate) = 12 AND Year(T14.Duedate) = 2010 AND T14.TransType in ('13','14') 
WHERE T0.CardType = 'C' 
Group By T0.CardCode, T0.CardName

-


Thanks,

Marli

Edited by: Marli Schutte on Aug 25, 2010 4:29 AM

Edited by: Marli Schutte on Aug 25, 2010 4:29 AM