gistfile1.sql

-- sum by year/month
SELECT subs.Year, subs.Month, 
	SUM(FirstOrderTotal) AS [FirstOrderTotal], 
	SUM(FuturOrderTotals) AS [FuturOrderTotals],
	SUM(LookupRenewalValue) AS [LookupRenewalValue],
	SUM(FactoredRenewaValue) AS [FactoredRenewaValue]
FROM (
	-- get first and future order totals
	SELECT cs.CustomerId, YEAR(cs.CreatedOn) AS [Year], MONTH(cs.CreatedOn) AS [Month], 
		MAX(cs.OrderSubtotalInclTax) AS FirstOrderTotal, 
		SUM(CASE 
				WHEN ISNULL(oo2.OrderTotal, 0) > 0 THEN 
					CASE		
						WHEN ISNULL(oo2.OrderTotal, 0) < ISNULL(oo2.OrderSubtotalInclTax, 0) THEN ISNULL(oo2.OrderTotal, 0)
						ELSE ISNULL(oo2.OrderSubtotalInclTax, 0)
					END
				ELSE ISNULL(oo2.OrderSubtotalInclTax, 0)
            END 
		) AS FuturOrderTotals,
		MAX(cs.SeatRenewalValue) AS LookupRenewalValue,
		MAX(cs.FactoredRenewaValue) AS FactoredRenewaValue
	FROM (
		-- turn Seats into distinct Subscriptions including OrderTotal and RenewalValue
		SELECT DISTINCT cs.CustomerId, cs.FirstOrderId, 
			oo1.CreatedOn,
			CASE 
				WHEN oo1.OrderTotal > 0 THEN 
					CASE		
						WHEN oo1.OrderTotal < oo1.OrderSubtotalInclTax THEN oo1.OrderTotal
						ELSE oo1.OrderSubtotalInclTax
					END
				ELSE oo1.OrderSubtotalInclTax
			END AS OrderSubtotalInclTax,
			(CASE 
				WHEN oo1.OrderTotal > 0 THEN 
					CASE		
						WHEN oo1.OrderTotal < oo1.OrderSubtotalInclTax THEN oo1.OrderTotal
						ELSE oo1.OrderSubtotalInclTax
					END
				ELSE oo1.OrderSubtotalInclTax
			END * .6) AS [FactoredRenewaValue],
			(SELECT SUM(cs2.SeatRenewalValue) FROM xam_Customer_Subscriptions_Rollup cs2 WHERE cs2.OrderId = cs.FirstOrderId) AS [SeatRenewalValue]
		FROM xam_Customer_Subscriptions_Rollup cs 
		INNER JOIN Nop_Order oo1 ON cs.FirstOrderId = oo1.OrderID
		WHERE cs.Ignore = 0 
		AND NOT EXISTS(
			SELECT *
			FROM Nop_Order oo
			LEFT OUTER JOIN Nop_OrderProductVariant opv ON oo.OrderID = opv.OrderID
			LEFT OUTER JOIN Nop_ProductVariant pv ON opv.ProductVariantID = pv.ProductVariantId
			WHERE oo.OrderId = cs.FirstOrderId
			AND pv.Sku IN ('mt pro', 'ma pro', 'xa ind', 'xi ind', 'xm ind')
		)
	) cs 
	LEFT OUTER JOIN Nop_Order oo2 ON cs.CustomerId = oo2.CustomerId AND cs.FirstOrderId <> oo2.OrderID 
	GROUP BY cs.CustomerId, YEAR(cs.CreatedOn), MONTH(cs.CreatedOn)
	-- ORDER BY YEAR(cs.CreatedOn), MONTH(cs.CreatedOn), MAX(cs.OrderSubtotalInclTax) 
) subs
GROUP BY subs.Year, subs.Month
ORDER BY subs.Year, subs.Month