sold.sql

SELECT 
	p.ProductID,
	p.ProductName,
	p.Size,
	p.Color,
	sold.Quantity,
	inv.Quantity as OnHand,
	sold.COGS,
	sold.Net,
	sold.Tax,
	sold.Gross	
FROM
-- products sold
( SELECT 
	ip.ProductID,	
	sum(ip.Quantity) as Quantity,
	sum(ip.Quantity * ip.ProductCostPriceAtSale) as COGS,
	sum(ip.Quantity * (ip.ProductPrice - ip.ProductDiscount)) as Net,
	sum(ip.ProductTax) as Tax,
	sum((ip.Quantity * (ip.ProductPrice - ip.ProductDiscount)) + ip.ProductTax) as Gross	
FROM
	-- Calculate real quantity of products sold taking into consideration refunds
	(
		SELECT 
			iprod.InvoiceID, 
			iprod.ProductID, 
			iprod.ProductPrice,
			iprod.ProductTax,
			iprod.ProductDiscount,
			iprod.ProductCostPriceAtSale,			
			Quantity = 
				CASE 
					WHEN iref.QuantityRefunded IS NULL THEN iprod.Quantity
					WHEN iref.QuantityRefunded IS NOT NULL THEN (iprod.Quantity - iref.QuantityRefunded)
				END
		FROM
			Invoice_Products iprod
			LEFT JOIN Invoice_Refunds iref ON iprod.InvoiceID = iref.InvoiceID and iprod.ProductID = iref.ProductID		
	) as ip
	INNER JOIN Invoices i ON ip.InvoiceID = i.InvoiceID		
WHERE
	i.LocationId IN (1)
	AND i.InvoiceTypeId = 1
GROUP BY
	ip.ProductID
) AS sold
INNER JOIN Inventory inv ON sold.ProductID = inv.ProductID
INNER JOIN Products p ON sold.ProductID = p.ProductID