SELECT
CONCAT(customers.LASTNAME,', ',customers.FIRSTNAME) AS NAME,
agreements.FORM_TITLE AS FORM,
CONCAT(Date(agreements.COMPLETION_DATE),', ',Time(agreements.COMPLETION_DATE)) AS 'Completed',
IF(
        IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, agreements.USED_DATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, agreements.USED_DATE)/60,1))>=120,
        NULL,
        IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, agreements.USED_DATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, agreements.USED_DATE)/60,1))
) AS 'Accepted (minutes)',
IF(
        IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, i2.POSTDATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, i2.POSTDATE)/60,1))>=120,
        NULL,
        IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, i2.POSTDATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, i2.POSTDATE)/60,1))
) AS 'Purchase (minutes)',
IF(
        IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, c2.POSTDATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, c2.POSTDATE)/60,1))>=120,
        NULL,
        IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, c2.POSTDATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, c2.POSTDATE)/60,1))
) AS 'Check-in (minutes)'
FROM
agreements
LEFT JOIN (
        SELECT
        c.CUSTOMER_ID,
        c.POSTDATE,
        CONCAT(c.CUSTOMER_ID,'-',DATE(c.POSTDATE))
        FROM (
                SELECT
                *
                FROM
                checkins
                WHERE
                date(checkins.POSTDATE) >= '{STARTDATE}' AND
                date(checkins.POSTDATE) <= '{ENDDATE}'
                ORDER BY
                checkins.POSTDATE ASC
        ) c
        GROUP BY
        CONCAT(c.CUSTOMER_ID,'-',DATE(c.POSTDATE))
) AS c2 ON c2.CUSTOMER_ID = agreements.CUSTOMER_ID AND DATE(c2.POSTDATE) = DATE(agreements.COMPLETION_DATE)
LEFT JOIN (
        SELECT
        i.CUSTOMER_ID,
        i.POSTDATE,
        CONCAT(i.CUSTOMER_ID,'-',DATE(i.POSTDATE)),
        i.AMOUNT
        FROM (
                SELECT
                *
                FROM
                invoices
                WHERE
                invoices.VOIDEDINVOICE = 0 AND
                DATE(invoices.POSTDATE) >= '{STARTDATE}' AND
                DATE(invoices.POSTDATE) <= '{ENDDATE}'
                ORDER BY
                invoices.POSTDATE ASC
        ) i
        GROUP BY
        CONCAT(i.CUSTOMER_ID,'-',DATE(i.POSTDATE))
) as i2 ON i2.CUSTOMER_ID = agreements.CUSTOMER_ID AND DATE(i2.POSTDATE) = DATE(agreements.COMPLETION_DATE)
INNER JOIN customers ON customers.CUSTOMER_ID = agreements.CUSTOMER_ID
WHERE
agreements.EMAILED_ON = '0000-00-00' AND
DATE(agreements.COMPLETION_DATE) >= '{STARTDATE}' AND
DATE(agreements.COMPLETION_DATE) <= '{ENDDATE}'
UNION
SELECT
'AVERAGE' AS NAME,
'' AS FORM,
'' AS 'Waiver Completed',
ROUND(AVG(
        IF(
                IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, agreements.USED_DATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, agreements.USED_DATE)/60,1))>=120,
                NULL,
                IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, agreements.USED_DATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, agreements.USED_DATE)/60,1))
        )
),1) AS 'Accepted (minutes)',
ROUND(AVG(
        IF(
                IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, i2.POSTDATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, i2.POSTDATE)/60,1))>=120,
                NULL,
                IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, i2.POSTDATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, i2.POSTDATE)/60,1))
        )
),1) AS 'Purchase (minutes)',
ROUND(AVG(
        IF(
                IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, c2.POSTDATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, c2.POSTDATE)/60,1))>=120,
                NULL,
                IF(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, c2.POSTDATE)<0,0,ROUND(TIMESTAMPDIFF(SECOND, agreements.COMPLETION_DATE, c2.POSTDATE)/60,1))
        )
),1) AS 'Check-in (minutes)'
FROM
agreements
LEFT JOIN (
        SELECT
        c.CUSTOMER_ID,
        c.POSTDATE,
        CONCAT(c.CUSTOMER_ID,'-',DATE(c.POSTDATE))
        FROM (
                SELECT
                *
                FROM
                checkins
                WHERE
                date(checkins.POSTDATE) >= '{STARTDATE}' AND
                date(checkins.POSTDATE) <= '{ENDDATE}'
                ORDER BY
                checkins.POSTDATE ASC
        ) c
        GROUP BY
        CONCAT(c.CUSTOMER_ID,'-',DATE(c.POSTDATE))
) AS c2 ON c2.CUSTOMER_ID = agreements.CUSTOMER_ID AND DATE(c2.POSTDATE) = DATE(agreements.COMPLETION_DATE)
LEFT JOIN (
        SELECT
        i.CUSTOMER_ID,
        i.POSTDATE,
        CONCAT(i.CUSTOMER_ID,'-',DATE(i.POSTDATE)),
        i.AMOUNT
        FROM (
                SELECT
                *
                FROM
                invoices
                WHERE
                invoices.VOIDEDINVOICE = 0 AND
                DATE(invoices.POSTDATE) >= '{STARTDATE}' AND
                DATE(invoices.POSTDATE) <= '{ENDDATE}'
                ORDER BY
                invoices.POSTDATE ASC
        ) i
        GROUP BY
        CONCAT(i.CUSTOMER_ID,'-',DATE(i.POSTDATE))
) as i2 ON i2.CUSTOMER_ID = agreements.CUSTOMER_ID AND DATE(i2.POSTDATE) = DATE(agreements.COMPLETION_DATE)
INNER JOIN customers ON customers.CUSTOMER_ID = agreements.CUSTOMER_ID
WHERE
agreements.EMAILED_ON = '0000-00-00' AND
DATE(agreements.COMPLETION_DATE) >= '{STARTDATE}' AND
DATE(agreements.COMPLETION_DATE) <= '{ENDDATE}'