SET @START='{STARTDATE}', @END='{ENDDATE}', @DATE='', @TYPE='';

SELECT * FROM (
	(
		SELECT
			DATE(ci.POSTDATE) DATE,
			CASE
				WHEN ci.DETAILS LIKE 'EFT%' THEN 'MEMBER'
				WHEN ci.DETAILS LIKE 'Prepaid%' THEN 'MEMBER'
				WHEN ci.DETAILS LIKE '%punches%' THEN 'PUNCH CARD'
				ELSE 'GUEST'
			END TYPE,
			COUNT(*) 'CHECK-INS',
			SUM(ci.FROM_MOBILE_APP) MOBILE_APP,
			SUM(IF(DATE(c.FIRST_CONTACT_DATE) = DATE(ci.POSTDATE),1,0)) FIRST_TIME_GUEST
		FROM
			checkins AS ci
		JOIN customers AS c ON c.CUSTOMER_ID = ci.CUSTOMER_ID
		WHERE
			DATE(ci.POSTDATE) >= @START AND
			DATE(ci.POSTDATE) <= @END
		GROUP BY
			DATE(ci.POSTDATE),
			CASE
				WHEN ci.DETAILS LIKE 'EFT%' THEN 'MEMBER'
				WHEN ci.DETAILS LIKE 'Prepaid%' THEN 'MEMBER'
				WHEN ci.DETAILS LIKE '%punches%' THEN 'PUNCH CARD'
				ELSE 'GUEST'
			END
		ORDER BY
			DATE(ci.POSTDATE) ASC,
			CASE
				WHEN ci.DETAILS LIKE 'EFT%' THEN 'MEMBER'
				WHEN ci.DETAILS LIKE 'Prepaid%' THEN 'MEMBER'
				WHEN ci.DETAILS LIKE '%punches%' THEN 'PUNCH CARD'
				ELSE 'GUEST'
			END ASC
	)

	UNION

	(
		SELECT
			DATE(ci.POSTDATE) DATE,
			'TOTAL' TYPE,
			COUNT(*) 'CHECK-INS',
			SUM(ci.FROM_MOBILE_APP) MOBILE_APP,
			SUM(IF(DATE(c.FIRST_CONTACT_DATE) = DATE(ci.POSTDATE),1,0)) FIRST_TIME_GUEST
		FROM
			checkins AS ci
		JOIN customers AS c ON c.CUSTOMER_ID = ci.CUSTOMER_ID
		WHERE
			DATE(ci.POSTDATE) >= @START AND
			DATE(ci.POSTDATE) <= @END
		GROUP BY
			DATE(ci.POSTDATE)
	)
)a
GROUP BY
a.DATE,
a.TYPE