Help with Query and PIVOT

[Former Member]

Hi Guys,

I have written this query to hopefully give me the Monthly Sales by Country, however I cannot get it to quite work correctly,

Does anyone have any thoughts as to where I have gone wrong. Help appreciated.

Select distinct Country,sum(SalesYTD) from

(SELECT distinct T2.Country, SUM(T1.Debit) - sum(T1.Credit) as SalesYTD, T1.Shortname, MONTH(T1.RefDate) as [Month] FROM JDT1 T1 INNER JOIN OCRD T2 ON T1.ShortName = T2.CardCode

WHERE Year(T1.RefDate) = 2011 AND T1.TransType IN ('13','14') and T2.CardType = 'C'

group by T2.Country,T1.REFDATE, T1.Shortname) sYTD

PIVOT (sum(SalesYTD) FOR [Month] IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

group by Country

Kind regards

Sean Martin

[Former Member]

Not to worry guys, resolved it

select * from (

SELECT DISTINCT Country, SUM(SalesYTD) as YTDSales, [MONTH] FROM

(

SELECT distinct T2.Country, SUM(T1.Debit) - SUM(T1.Credit) as SalesYTD, T1.Shortname, MONTH(T1.RefDate) as [Month] FROM JDT1 T1 INNER JOIN OCRD T2 ON T1.ShortName = T2.CardCode

WHERE Year(T1.RefDate) = 2011 AND T1.TransType IN ('13','14') and T2.CardType = 'C'

group by T2.Country, T1.Shortname,month(T1.RefDate))

sYTD

group by Country,[MONTH]

) Q

PIVOT (sum([YTDSales]) FOR [Month] IN

([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P