I have written the SQL query below as a product sales report, and it works perfectly, except for one thing....
I need for the query to also display those products which sold a quantity of zero (0) during the given date range, but I can't seem to figure it out....
Can someone point me in the right direction please?
SELECT dbo.OrderItem.ProductId,
dbo.Product.SKU,
dbo.Product.Name,
SUM(dbo.OrderItem.Quantity) AS [# Units Sold],
SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax) AS [Total Sales Revenue],
SUM(dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Product Cost],
SUM(dbo.OrderItem.Quantity * dbo.OrderItem.UnitPriceExclTax - dbo.OrderItem.Quantity * dbo.OrderItem.OriginalProductCost) AS [Total Profit]
FROM dbo.[Order] INNER JOIN
dbo.OrderItem ON dbo.[Order].Id = dbo.OrderItem.OrderId INNER JOIN
dbo.Product ON dbo.OrderItem.ProductId = dbo.Product.Id
WHERE (dbo.[Order].PaidDateUtc BETWEEN CONVERT(DATETIME, '2014-01-06 00:00:00', 102) AND CONVERT(DATETIME, '2014-07-25 00:00:00', 102))
GROUP BY dbo.OrderItem.ProductId, dbo.Product.SKU, dbo.Product.Name
Thanks,
Steve