SET @CUR=0; set @start = '{STARTDATE}'; SET @countRptDate = (SELECT COUNT(*) FROM daily_numbers AS d WHERE d.TAG = 'Members_All' AND d.POSTDATE = @start); SET @countDayBeforeRptDate = (SELECT COUNT(*) FROM daily_numbers AS d WHERE d.TAG = 'Members_All' AND d.POSTDATE = DATE_SUB(@start, INTERVAL 1 DAY)); SELECT CONCAT(x.POSTDATE,x.DATE) Date, x.MEMBERS, x.nDiff '#Diff', x.pDiff '%Diff' FROM ( SELECT 1, '' AS Date, postdate, @CUR:=sum(members) as 'Members', NULL 'nDiff', NULL 'pDiff' FROM ( SELECT Date_Format(@start,'%c/%e/%Y') AS POSTDATE, COUNT(*) MEMBERS FROM customers WHERE (customers.CUSTOMER_TYPE = 'MEMBER' OR customers.CUSTOMER_TYPE = 'CORPORATE') AND (customers.CURRENT_STATUS = 'OK' OR customers.CURRENT_STATUS = 'FROZEN') AND @countRptDate = 0 UNION SELECT Date_Format(d.POSTDATE,'%c/%e/%Y') AS POSTDATE, d.VALUE MEMBERS FROM daily_numbers d WHERE d.TAG = 'Members_All' AND d.POSTDATE = @start and @countRptDate > 0 ) curDay GROUP BY postdate UNION SELECT 2, ' | Previous Day' AS Date, postdate, sum(members), IF(@CUR-sum(members)>0,CONCAT('+',ROUND(@CUR-sum(members),0)),ROUND(@CUR-sum(members),0)) 'nDiff', IF(ROUND((@CUR/sum(members) -1)*100,1) > 0, CONCAT('+',ROUND((@CUR/sum(members) -1)*100,1), '%'),CONCAT(ROUND((@CUR/sum(members) -1)*100,1), '%')) as 'pDiff' FROM ( SELECT Date_Format(d.POSTDATE,'%c/%e/%Y') AS POSTDATE, d.VALUE MEMBERS FROM daily_numbers d WHERE d.TAG = 'Members_All' AND d.POSTDATE = DATE_SUB(@start, INTERVAL 1 DAY) and @countDayBeforeRptDate > 0 UNION SELECT Date_Format(DATE_SUB(@start, INTERVAL 1 DAY),'%c/%e/%Y') AS POSTDATE, COUNT(*) MEMBERS FROM customers WHERE (customers.CUSTOMER_TYPE = 'MEMBER' OR customers.CUSTOMER_TYPE = 'CORPORATE') AND (customers.CURRENT_STATUS = 'OK' OR customers.CURRENT_STATUS = 'FROZEN') AND @countDayBeforeRptDate = 0 ) priorDay GROUP BY postdate UNION SELECT CASE WHEN d.POSTDATE = DATE_SUB(@start, INTERVAL 1 MONTH) THEN 3 ELSE 4 END, CASE WHEN d.POSTDATE = DATE_SUB(@start, INTERVAL 1 MONTH) THEN ' | Previous Month' ELSE ' | Previous Year' END AS Date, Date_Format(d.POSTDATE,'%c/%e/%Y') as POSTDATE, d.VALUE MEMBERS, IF(@CUR-d.VALUE>0,CONCAT('+',ROUND(@CUR-d.`VALUE`,0)),ROUND(@CUR-d.VALUE,0)) 'nDiff', IF(ROUND(((@CUR/d.VALUE)-1)*100,1)>0,CONCAT('+',ROUND(((@CUR/d.VALUE)-1)*100,1),'%'),CONCAT(ROUND(((@CUR/d.VALUE)-1)*100,1),'%')) 'pDiff' FROM daily_numbers AS d WHERE d.TAG = 'Members_All' AND (d.POSTDATE = DATE_SUB(@start, INTERVAL 1 MONTH) OR d.POSTDATE = DATE_SUB(@start, INTERVAL 1 YEAR)) UNION (SELECT 5, ' | Record High' as Date, DATE_FORMAT(d.POSTDATE,'%c/%e/%Y') as POSTDATE, d.VALUE MEMBERS, IF(@CUR-d.`VALUE`>0,CONCAT('+',ROUND(@CUR-d.`VALUE`,0)),ROUND(@CUR-d.`VALUE`,0)) 'nDiff', IF(ROUND(((@CUR/d.`VALUE`)-1)*100,1)>0,CONCAT('+',ROUND(((@CUR/d.`VALUE`)-1)*100,1),'%'),CONCAT(ROUND(((@CUR/d.`VALUE`)-1)*100,1),'%')) 'pDiff' FROM daily_numbers AS d WHERE d.TAG = 'Members_All' ORDER BY d.VALUE DESC LIMIT 1) order by 1 )x