Hi, Christian,

Glad you took a crack at it. PIVOT is perfect for something like this.

As Naomi pointed out in her reply, you want to be careful about using a WHERE clause on a table involved in an OUTER JOIN.

Stripped down to the bare essentials....if you do this

SELECT *
FROM MasterTable
LEFT OUTER JOIN ChildTable ON MasterTable.ProductPK = ChildTable.ProductFK
WHERE ChildTable.DollarAmount > 100

in other words, if you query from Table A....and then do a left outer join to Table B....and then use a WHERE clause on a column from Table B....effectively you lose the behavior of an OUTER JOIN and you've turned the query into an INNER JOIN. Probably not your intention.

So you need to "localize" (my expression) the condition...

SELECT *
FROM MasterTable
LEFT OUTER JOIN ChildTable ON MasterTable.ProductPK = ChildTable.ProductFK AND ChildTable.DollarAmount > 100


And on her other point, if you have date indexes, you'll want to use BETWEEN with a date range, as opposed to using DATEPART with Year. You'll potentially get better performance.