Note that the query does not exclude cancelled orders, etc.
Run the query without any date filter (i.e. remove AND (dbo.[Order].PaidDateUtc BETWEEN ...)
Then, get a few ProductId's your concerned with, and compare the results with this very simple query that only uses the OrderItem table
SELECT dbo.OrderItem.ProductId,
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.OrderItem
WHERE ProductId in (5,7,31,51)
GROUP BY dbo.OrderItem.ProductId
ORDER BY dbo.OrderItem.ProductId
(replace the product ids)
Run the query without any date filter (i.e. remove AND (dbo.[Order].PaidDateUtc BETWEEN ...)
Then, get a few ProductId's your concerned with, and compare the results with this very simple query that only uses the OrderItem table
SELECT dbo.OrderItem.ProductId,
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.OrderItem
WHERE ProductId in (5,7,31,51)
GROUP BY dbo.OrderItem.ProductId
ORDER BY dbo.OrderItem.ProductId
(replace the product ids)
OK, I see what you mean.
So, then, I guess the query needs to also filter results to exclude deleted or cancelled orders...or maybe just filter on the OrderStatusId being 'completed' ??
I ran the query again like this:
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.Product
LEFT JOIN dbo.OrderItem
ON dbo.Product.Id = dbo.OrderItem.ProductId
LEFT JOIN dbo.[Order]
ON dbo.OrderItem.OrderId = dbo.[Order].Id
AND (dbo.[Order].PaidDateUtc BETWEEN CONVERT(DATETIME, '2014-11-01 00:00:00', 102) AND CONVERT(DATETIME, '2014-11-30 00:00:00', 102))
WHERE dbo.Product.Published=1
AND dbo.Product.Deleted <> 1
GROUP BY dbo.OrderItem.ProductId, dbo.Product.SKU, dbo.Product.Name
ORDER BY dbo.OrderItem.ProductId, dbo.Product.SKU, dbo.Product.Name
..and in the results, for example, product id#279 shows that we sold 2065 units, when in reality, we sold and shipped less than 200 units that month.
279
729
Metatarsal Pad - Felt
2065
17706.1800
5947.2000
11758.9800
We only cancelled maybe a dozen orders for the entire month.
I'm not certain, but my numbers sold in the query results are so much higher than what we really shipped, I can only imagine it is incomplete guest orders being counted or something???
I suspect that just prior to running the query, maintenance should be performed that deletes incompleted guest orders...which, I assume, also deletes the associated OrderItem records.
