One Year Sales Analysis Report

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