idrL5dH5

1DROP TEMPORARY TABLE IF EXISTS MRReport;
2# Create temp table
3CREATE TEMPORARY TABLE MRReport
4# Insert Initial Data
5SELECT
6	# MRR Year
7	`mntMRR`.MrrYear,
8	# MRR Month
9	`mntMRR`.MrrMonth,
10	# MRR Total
11	SUM(`mntMRR`.totalCost) AS mntMRRTotal
12FROM (
13	SELECT 
14		# payment amount total
15		( CASE 
16			WHEN mrr.periodicity = 1 THEN 
17				ROUND(mrr.total, 2)
18			WHEN mrr.periodicity = 12 THEN 
19				ROUND((mrr.total / 12), 2)
20			END
21		) AS totalCost,
22		# payment period interval
23		mrr.periodicity AS prd,
24		# payment month
25		MONTH(mrr.entry) AS MrrMonth,
26		# payment year
27		YEAR(mrr.entry) AS MrrYear
28	FROM
29		`mrr` AS mrr
30	LEFT JOIN `account` AS acnt ON acnt.id	= mrr.account
31) AS `mntMRR`
32GROUP BY `mntMRR`.MrrYear, `mntMRR`.MrrMonth;
33# Project Nov 2017 Payments
34INSERT INTO MRReport
35SELECT
36	# MRR Year
37	`mntMRR`.MrrYear,
38	# MRR Month
39	`mntMRR`.MrrMonth,
40	# MRR Total
41	SUM(`mntMRR`.totalCost) AS mntMRRTotal
42FROM (
43	SELECT 
44		# payment amount total
45		( CASE 
46			WHEN mrr.periodicity = 1 THEN 
47				ROUND(mrr.total, 2)
48			WHEN mrr.periodicity = 12 THEN 
49				ROUND((mrr.total / 12), 2)
50			END
51		) AS totalCost,
52		# payment period interval
53		mrr.periodicity AS prd,
54		# payment month
55		11 AS MrrMonth,
56		# payment year
57		YEAR(mrr.entry) AS MrrYear
58	FROM
59		`mrr` AS mrr
60	LEFT JOIN `account` AS acnt ON acnt.id	= mrr.account
61	WHERE 
62		mrr.billing_end NOT LIKE '2017-11%'
63		AND acnt.next_bill LIKE '2017-11%' 
64	GROUP BY mrr.account ORDER BY mrr.id DESC
65) AS `mntMRR`;
66# Build Report
67SELECT MrrYear, MrrMonth, concat('$',FORMAT(SUM(mntMRRTotal), 2)) FROM MRReport 
68GROUP BY MrrYear, MrrMonth;