SELECT
    DATETIME(BASE.period) AS periodStart,
    :periodEndQR ,
    BASE.roster_id AS scheduleId,
    BASE.roster_name AS scheduleName,
    BASE.roster_description AS scheduleDescription,
    IFNULL(DATASET.appointment_count,0) AS appointmentCount,
    IFNULL(DATASET.attendance,0) AS attendanceCount ,
    IFNULL(DATASET.access,0) AS accessCount,
    IFNULL(DATASET.present,0) AS presentCount
FROM
    (
        SELECT
    period,
    R.roster_id,
    R.roster_name,
    R.roster_description
    FROM
        (
            SELECT
                :periodBASE
            FROM
                cloud_date_table PC
            WHERE
                PC.cloud_date BETWEEN DATETIME(:fromParam) AND DATETIME(:toParam)
                GROUP BY period
                LIMIT :limitParam OFFSET :offsetParam) AS PC
	CROSS JOIN roster R
WHERE
    R.customer_id = :customerParam
    AND R.deleted_date IS NULL
    :ROSTER_FILTER
        ) AS BASE
    LEFT JOIN
    (
SELECT
    roster_id,
    period,
    COUNT(appointment_id) AS appointment_count,
    SUM(attendance) AS attendance,
    SUM(access) AS access,
    SUM(present) AS present
FROM
    (
    SELECT
    roster_id,
    appointment_id,
    :periodDATASET,
    SUM(attendance) >0 AS attendance,
    SUM(access) >0 AS access,
    SUM(present)> 0 AS present
    FROM
    (
    SELECT
    ri.roster_id,
    va.id AS appointment_id,
    e.event_id,
    CASE
    WHEN (e.event_type = 0
    OR e.event_type = 1) THEN 1
    ELSE 0
    END AS attendance,
    CASE
    WHEN (e.event_type = 2) THEN 1
    ELSE 0
    END AS access,
    CASE
    WHEN (e.event_id IS NOT NULL) THEN 1
    ELSE 0
    END AS present,
    CASE
    WHEN (t.task_id IS NOT NULL
    AND (STRFTIME ('%s',
    va.appointment_start) -STRFTIME ('%s',
    DATE(va.appointment_start))) < t.start_time_seconds) THEN DATE(va.appointment_start, '-1 days')
    ELSE DATE(va.appointment_start)
    END AS appointment_day,
    va.appointment_start AS appoointment_start,
    t.start_time_seconds
    FROM
    visitor_appointments va
    INNER JOIN roster_item ri ON va.attendant_roster_item_id = ri.roster_item_id
    LEFT JOIN task t ON ri.task_id = t.task_id
    LEFT JOIN roster R ON ri.roster_id = R.roster_id
    LEFT JOIN eventlog e ON va.id = e.visit_appointment AND e.is_deleted = FALSE
    WHERE
    va.customer_id = :customerParam
    AND va.appointment_start BETWEEN :fromParam AND DATE(:toParam, '+1 days')
    AND R.deleted_date IS NULL
    :ROSTER_FILTER
    )
    GROUP BY
    roster_id,
    appointment_id,
    appointment_day)
GROUP BY
    roster_id,
    period
) AS DATASET

ON
    DATASET.period = BASE.period
    AND DATASET.roster_id = BASE.roster_id