Skip to Content
Former Member
Nov 22, 2012 at 01:21 PM

Problem With Query


Hi Guys,

I am trying to write a query so that I can show the Customer Sales only on a month by month basis for the financial year against the SlpCode/SlpName associated with the account held in Item Master Data, however I seemed to have made an error or two and cannot get it to work, it is also pulling in supplier accounts which I do not want !

Could someone please give me a pointer or two as to where I have gone wrong :

SELECT P.[CardCode],P.[CardName],P.[SlpCode],P.[SlpName],

(SELECT SUM(Debit) - sum(Credit) FROM JDT1 WHERE ShortName= P.CardCode AND Year(DueDate) = 2012 AND TransType IN ('13','14')) '2012 Sales',
[1] as [Jan],
[2] as [Feb],
[3] as [Mar],
[4] as [Apr],
[5] as [May],
[6] as [Jun],
[7] as [Jul],
[8] as [Aug],
[9] as [Sep],
[10] as [Oct],
[11] as [Nov],
[12] as [Dec]

FROM (SELECT T0.CARDCODE, T0.SlpCode, T0.CARDNAME, (T1.Debit - T1.Credit) AS [BAL],
MONTH(T1.RefDate) as [month]
Left join dbo.oslp T2 On T0.SlpCode = T2.SlpCode
LEFT JOIN dbo.JDT1 T1 ON T1.ShortName = T0.CardCode AND T0.CardType = 'C' AND Year(T1.RefDate) = 2012 AND T1.TransType in ('13','14')) S
PIVOT (SUM(S.[BAL]) FOR [month] IN
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

Many thanks in Advance