How To Include The Nulls??

Hi, I have the following query stored:

SELECT dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, SUM(dbo.OrderDetails_Retail.Quantity) AS ProdQtyPerWeek, DATEPART(wk,
dbo.Orders_Retail.OrderDate) AS SalesWeek, YEAR(dbo.Orders_Retail.OrderDate) AS SalesYear
FROM dbo.OrderDetails_Retail INNER JOIN
dbo.Orders_Retail ON dbo.OrderDetails_Retail.OrderID = dbo.Orders_Retail.OrderID
WHERE (dbo.Orders_Retail.account = @Account) AND (dbo.Orders_Retail.OrderStatus <> 'Deleted') AND (dbo.Orders_Retail.PayStatus <> 'Pending') AND
(dbo.Orders_Retail.OrderStatus <> 'Refunded') AND (DATEDIFF(d, dbo.Orders_Retail.OrderDate, @StartDate) <= 0) AND (DATEDIFF(d,
dbo.Orders_Retail.OrderDate, @EndDate) >= 0)
GROUP BY YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk, dbo.Orders_Retail.OrderDate), dbo.OrderDetails_Retail.ProductID,
dbo.OrderDetails_Retail.ProductName
ORDER BY dbo.OrderDetails_Retail.ProductID, dbo.OrderDetails_Retail.ProductName, YEAR(dbo.Orders_Retail.OrderDate), DATEPART(wk,
dbo.Orders_Retail.OrderDate)



Basically, it will return a load of results grouped by product for how much qty of that product was sold per week during a date range...

As my client wants to select multiple products at once to compare rather than do it in my application (I'm building something in ASP), I thought I might be able to do it on the database side.



The problem with the above is that.. lets say I select a date range that has weeks 1-4 in it.

Product 1 only sold qty's for weeks 1-2, product 2 sold for only week 3 and product 4 sold in all four weeks.

I'd get

Prod | Qty | Week

1 23 1

1 12 2

2 10 3

3 22 1

3 15 2

3 12 3

3 4 4

Although this looks fine - what I actually need is:

1 23 1

1 12 2

1 0 3

1 0 3

2 0 1

2 0 2

2 10 3

2 0 4

3 22 1

3 15 2

3 12 3

3 4 4

Does that make sense?



Any ideas on how to do this?