mrr-report.sql

DROP TEMPORARY TABLE IF EXISTS MRReport;
# Create temp table
CREATE TEMPORARY TABLE MRReport
# Insert Initial Data
SELECT
	# MRR Year
	`mntMRR`.MrrYear,
	# MRR Month
	`mntMRR`.MrrMonth,
	# MRR Total
	SUM(`mntMRR`.totalCost) AS mntMRRTotal
FROM (
	SELECT 
		# payment amount total
		( CASE 
			WHEN mrr.periodicity = 1 THEN 
				ROUND(mrr.total, 2)
			WHEN mrr.periodicity = 12 THEN 
				ROUND((mrr.total / 12), 2)
			END
		) AS totalCost,
		# payment period interval
		mrr.periodicity AS prd,
		# payment month
		MONTH(mrr.entry) AS MrrMonth,
		# payment year
		YEAR(mrr.entry) AS MrrYear
	FROM
		`mrr` AS mrr
	LEFT JOIN `account` AS acnt ON acnt.id	= mrr.account
) AS `mntMRR`
GROUP BY `mntMRR`.MrrYear, `mntMRR`.MrrMonth;
# Project Nov 2017 Payments
INSERT INTO MRReport
SELECT
	# MRR Year
	`mntMRR`.MrrYear,
	# MRR Month
	`mntMRR`.MrrMonth,
	# MRR Total
	SUM(`mntMRR`.totalCost) AS mntMRRTotal
FROM (
	SELECT 
		# payment amount total
		( CASE 
			WHEN mrr.periodicity = 1 THEN 
				ROUND(mrr.total, 2)
			WHEN mrr.periodicity = 12 THEN 
				ROUND((mrr.total / 12), 2)
			END
		) AS totalCost,
		# payment period interval
		mrr.periodicity AS prd,
		# payment month
		11 AS MrrMonth,
		# payment year
		YEAR(mrr.entry) AS MrrYear
	FROM
		`mrr` AS mrr
	LEFT JOIN `account` AS acnt ON acnt.id	= mrr.account
	WHERE 
		mrr.billing_end NOT LIKE '2017-11%'
		AND acnt.next_bill LIKE '2017-11%' 
	GROUP BY mrr.account ORDER BY mrr.id DESC
) AS `mntMRR`;
# Build Report
SELECT MrrYear, MrrMonth, concat('$',FORMAT(SUM(mntMRRTotal), 2)) FROM MRReport 
GROUP BY MrrYear, MrrMonth;