SET
	@STARTDATE = '{STARTDATE}',
	@ENDDATE = '{ENDDATE}';

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 '% punch%' 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 ci
			LEFT JOIN customers c ON ci.CUSTOMER_ID = c.CUSTOMER_ID 
		WHERE
			DATE( ci.POSTDATE ) >= @STARTDATE 
			AND DATE( ci.POSTDATE ) <= @ENDDATE
			AND ci.WHERE_DATABASE_TAG = 
				(
				SELECT s.VALUE
				FROM settings s
				WHERE s.NAME = 'LocalDatabaseWhereTag'
				)
		GROUP BY
			DATE( ci.POSTDATE ),
			CASE
				WHEN ci.DETAILS LIKE 'EFT%' THEN 'MEMBER' 
				WHEN ci.DETAILS LIKE 'Prepaid%' THEN 'MEMBER' 
				WHEN ci.DETAILS LIKE '% punch%' 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 '% punch%' 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 ci
			LEFT JOIN customers c ON ci.CUSTOMER_ID = c.CUSTOMER_ID
		WHERE
			DATE( ci.POSTDATE ) >= @STARTDATE 
			AND DATE( ci.POSTDATE ) <= @ENDDATE
			AND ci.WHERE_DATABASE_TAG = 
				(
				SELECT s.VALUE
				FROM settings s
				WHERE s.NAME = 'LocalDatabaseWhereTag'
				)
		GROUP BY
		DATE( ci.POSTDATE )
	)) a 
GROUP BY
	a.DATE,
	a.TYPE
;