Gianluca Sartori (1/11/2012)

---

Maybe, indented properly it's less confusing: 😉

WITH MeaningfulCTEName AS (

SELECT c.owneridname as BSC,

z.Tpl_TotalGWP as Total2012,

a.tpl_traderref as TraderRef,

a.name as CompanyName,

LEFT(z.Tpl_Month, 4) AS Year,

RIGHT(z.Tpl_Month, 2) AS Month,

ROW_NUMBER() OVER(PARTITION BY c.owneridname ORDER BY z.Tpl_TotalGWP DESC) AS rn

FROM dbo.Tpl_zonegwp z

INNER JOIN dbo.Account a

ON z.Tpl_CompanyId = a.accountid

INNER JOIN dbo.Contact c

ON a.ownerid = c.ownerid

INNER JOIN (

SELECT CurrentYear, CurrentMonth, CurrentYear - 1 AS LastYear

FROM (

SELECT LEFT(MaxMonth, 4) AS CurrentYear, RIGHT(MaxMonth, 2) AS CurrentMonth

FROM (

SELECT MAX(Tpl_Month) AS MaxMonth

FROM TowergateAgency_MSCRM.dbo.FilteredTpl_zonegwp AS Tpl_zonegwpExtensionBase_1

) AS MaxMonthQuery

) as MaxMonthQuery2

) AS b

ON left(z.tpl_month,4) = b.currentYear

AND RIGHT(z.Tpl_Month, 2) <= b.CurrentMonth

)

SELECT BSC,TraderRef,CompanyName,Total2012

FROM MeaningfulCTEName

WHERE rn=1

GROUP BY BSC,TraderRef,CompanyName,Total2012;

Do yourself a favour: use meaningful aliases names in your queries. What does A mean to you?

Could you post DDL (CREATE TABLE) statements for all the tables in the query, some sample data (INSERT statements with a few rows for each table) and the expected results based on your sample data?

If in doubt, read the article linked in my signature line and find out how to post to get fast answers.