SELECT
    :onparam AS calculated_on,
    *
FROM
    (
        SELECT
            BASE.appointment_id,
            BASE.appointment_day,
            BASE.appointment_timezone AS appointment_timezone,
            DATETIME(DATE(BASE.appointment_day), (-BASE.appointment_timezone + BASE.shift_start_second)|| ' seconds' ) AS appointment_shift_start,
            DATETIME(DATE(BASE.appointment_day), (-BASE.appointment_timezone + BASE.shift_end_second)|| ' seconds' ) AS appointment_shift_end,
            DATETIME(DATE(BASE.appointment_day), (-BASE.appointment_timezone + BASE.cin_start)|| ' seconds' ) AS appointment_cin_start,
            DATETIME(DATE(BASE.appointment_day), (-BASE.appointment_timezone + BASE.cout_end)|| ' seconds' ) AS appointment_cout_end
        FROM
            (
                SELECT
                    va.id AS appointment_id,
                    va.time_zone AS appointment_timezone,
                    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,

                    t.start_time_seconds AS shift_start_second,

                    CASE
                    WHEN (t.start_time_seconds >= t.end_time_seconds) THEN t.end_time_seconds + 86400
                    ELSE t.end_time_seconds
                    END AS shift_end_second,

                    CASE
                    WHEN (t.checkin_start IS NOT NULL) THEN
                    (CASE
                    WHEN (t.start_time_seconds >= t.checkin_start) THEN t.checkin_start
                    ELSE t.checkin_start - 86400
                    END)
                    ELSE
                    (CASE
                    WHEN (t.start_time_seconds >= t.end_time_seconds) THEN t.end_time_seconds
                    ELSE t.end_time_seconds - 86400
                    END)
                    END AS cin_start,

                    CASE
                    WHEN (t.checkout_end IS NOT NULL) THEN
                    (CASE
                    WHEN ((CASE
                    WHEN (t.start_time_seconds >= t.end_time_seconds) THEN t.end_time_seconds + 86400
                    ELSE t.end_time_seconds
                    END) < t.checkout_end) THEN t.checkout_end
                    ELSE t.checkout_end + 86400
                    END)
                    ELSE
                    t.start_time_seconds + 86400
                    END AS cout_end
                FROM
                    visitor_appointments va
                    LEFT 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
                WHERE
                    va.appointment_start BETWEEN DATE(:onparam , '-2 DAYS') AND DATE(:onparam , '+2 DAYS')
                  AND va.request_status = 'APPROVED'
                  AND va.customer_id = :customerparam

            ) AS BASE) AS CURRENT_APPOINTMENTS
WHERE
        CURRENT_APPOINTMENTS.appointment_cin_start <= :onparam
  AND CURRENT_APPOINTMENTS.appointment_cout_end > :onparam