SELECT
    COUNT(BASE.appointment_id) AS total_appointments,
    SUM(CASE WHEN (BASE.appointment_status IS NULL AND BASE.eventcount = 0 AND BASE.appointment_ended = 0) THEN 1 ELSE 0 END) AS pending_appointments,
    SUM(CASE WHEN (BASE.appointment_status IS NULL AND BASE.eventcount = 0 AND BASE.appointment_ended = 1) THEN 1 ELSE 0 END) AS not_attended_appointments,
    SUM(CASE WHEN (BASE.appointment_status IS NULL AND BASE.eventcount > 0) THEN 1 ELSE 0 END) AS progress_appointments,
    SUM(CASE WHEN (BASE.appointment_status = 'COMPLETED') THEN 1 ELSE 0 END) AS completed_appointments,
    SUM(CASE WHEN (BASE.appointment_status = 'CANCELED') THEN 1 ELSE 0 END) AS canceled_appointments
FROM
    (
        SELECT
            BASE.appointment_id,
            BASE.appointment_status,
            CASE
                WHEN :onparam < DATETIME(BASE.appointment_end, (-BASE.appointment_timezone)|| ' seconds' ) THEN 0
                ELSE 1
                END AS appointment_ended,
            CASE
                WHEN BASE.first_checkin_time IS NOT NULL
                    OR BASE.last_checkout_time IS NOT NULL
                    OR BASE.access_count >0 THEN 1
                ELSE 0
                END AS eventcount
        FROM
            (
                :VISIT_APPOINTMENT_REPORT
            ) AS BASE ) AS BASE