Skip to Content
0
Former Member
Dec 08, 2011 at 12:03 PM

Year as a Selectable Parameter

31 Views

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