I think I'm beign stupid here , the Where's not working and I cant see why ,
DECLARE @year date
SET @year = '20140101' --GETDATE()
--guarantee @year is Jan 01 00:00:00.
SET @year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @year), 0)
SELECT Products.ProductId, Products.ProductDescription,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 00, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 01, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Jan,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 01, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 02, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Feb,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 02, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 03, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Mar ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 03, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 04, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Apr,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 04, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 05, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS May,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 05, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 06, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS June ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 06, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 07, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS July,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 07, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 08, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Aug,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 08, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 09, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Sep ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 09, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 10, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Oct ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 10, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 11, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Nov,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 11, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 12, @year)
THEN SalesInvoiceItems.Quantity ELSE 0 END) AS Dec
FROM SalesInvoices INNER JOIN
SalesInvoiceItems ON SalesInvoices.SalesInvoice = SalesInvoiceItems.SalesInvoice INNER JOIN
Products ON SalesInvoiceItems.Product = Products.Product
WHERE SalesInvoices.EffectiveDate >= @Year_ AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 12, @Year_)
GROUP BY Products.ProductId, Products.ProductDescription
Order BY Products.ProductId DESC