Since you failed to provide DDL, sample data, and expected results, I had to guess at what you wanted. I also had to use the AdventureWorks database.

The following code should give you an idea how to get all of the columns that I think that you need. I don't want to put any further work into this until you have provided the requested info.

I also had to hard-code "Today", because the AdventureWorks DB is several years out of date by now.

DECLARE @Today AS Datetime

SET @Today = '2004-07-31' -- The Max Order Date in AdventureWorks

;

WITH CustSumm AS (

SELECT CustomerID

, Year(OrderDate) AS OrderYear

, Sum(

CASE

WHEN Month(OrderDate) = Month(@Today)

THEN TotalDue

ELSE 0

END

) AS PMTD

, Sum(

CASE

WHEN DatePart(y, OrderDate) = DateAdd(Year, DateDiff(Year, 0, @Today) - 1, 0 )

GROUP BY CustomerID, Year(OrderDate)

)

SELECT *

FROM CustSumm AS a

LEFT OUTER JOIN CustSumm AS b

ON a.CustomerID = b.CustomerID

AND a.OrderYear = b.OrderYear + 1

WHERE a.OrderYear = Year(@Today)

Drew