I'm trying to calculate Last Month SALES ...
STORED PROC
-----------------
DECLARE @DateTo DATETIME
SET @DateFrom = '7/01/2012 00:00:00'
SET @DateTo = '7/31/2012 23:59:59'
SELECT DISTINCT pv.Sku, p.Name, SUM(op.Quantity) AS QTY,
SUM(op.Quantity * op.UnitPriceExclTax) AS SubTotal
FROM dbo.[Order] o
INNER JOIN dbo.[OrderProductVariant] op ON o.ID = op.OrderId
INNER JOIN dbo.[ProductVariant] pv ON op.ProductVariantId = pv.Id
INNER JOIN dbo.[Product] p ON pv.ProductId = p.Id
WHERE o.Deleted = 0
AND o.OrderStatusID > 10
AND o.RefundedAmount = 0
AND o.CreatedOnUtc BETWEEN @DateFrom AND @DateTo
GROUP BY pv.Sku, p.Name, pv.ProductCost, pv.Price
ORDER BY pv.SKU;
=========================
I was assuming that ANY Coupon Discount AMOUNT would be stored somewhere maybe on the [OrderProductVariant] Table.
but this is not the case.
Any tip where or how to calcuate the REAL value of last month sales, taking in consideration also Discounts, Coupons .... ?