Hi Guys,
I have the following query to show me the sales per month by country, However at the moment I have the year is hard coded in and I would like to have it as a selectable parameter, I am having a complete brain blank and cannot get it to work, could someone please point me in the right direction.
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
Any help gratefully appreciated
Kind regards
Sean