The optimization process and recommendations:

1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

ALTER TABLE `Client_Custom_Data` ADD INDEX `client_data_idx_shisid` (`SHISID`);
ALTER TABLE `Clients` ADD INDEX `clients_idx_shisid_last_name` (`SHISID`,`Last_Name`);
ALTER TABLE `DAILY_LOG_DATA` ADD INDEX `daily_data_idx_srvid_sched_time` (`SRVID`,`Sched_Time`);

The optimized query:

SELECT
        c.Client_ID,
        c.Last_Name,
        c.First_Name,
        d.Sched_Time,
        CASE 
            WHEN d.Eventstatus = 1 THEN d.Time_In 
            ELSE NULL END AS Time_In,
CASE 
    WHEN d.Eventstatus = 1 THEN d.Time_Out 
    ELSE NULL END AS Time_Out,
CASE 
    WHEN d.Eventstatus = 1 THEN (d.Time_Out - d.Time_In) * 60 * 24 
    ELSE NULL END AS Mins,
CASE 
    WHEN d.Eventstatus = 1 THEN (Round(d.Sched_Duration / 15,
    1)) 
    ELSE NULL END AS Units,
CASE 
    WHEN d.Eventstatus = 1 THEN 'Completed' 
    WHEN d.Eventstatus = 10 THEN 'Cancelled: No Show' 
    ELSE TO_CHAR(d.Eventstatus) END AS Appt_Status,
CASE 
    WHEN d.SRVID = 37913 THEN 'OASAS - Toxicology Rapid Screen' 
    ELSE TO_CHAR(d.SRVID) END AS Program,
CASE 
    WHEN d.Eventstatus = 1 THEN 'Y' 
    ELSE 'N' END AS Billable,
CASE 
    WHEN d.ProvidersID = 112906 THEN 'Some Name' 
    ELSE TO_CHAR(d.ProvidersID) END AS Provider,
CASE 
    WHEN d.Activity_Desc = 'OASAS   (Group)' THEN 'WGACS' 
    WHEN d.Activity_Desc = 'OASAS   (Individual)' THEN 'WGACS' 
    ELSE d.Activity_Desc END AS Location_ID 
FROM
Clients c 
JOIN
DAILY_LOG_DATA d 
    ON c.SHISID = d.SHISID 
JOIN
Client_Custom_Data cd 
    ON d.SHISID = cd.SHISID 
WHERE
d.SRVID IN (
    37913, 36186, 36185, 36180, 36179, 36168, 36167, 36182, 36181, 36173, 36172, 36177, 36176, 36175, 36174, 36178, 36184, 36183, 36188, 36187
) 
AND c.Last_Name != 'TEST' 
AND d.Sched_Time >= TO_DATE('2020-09-28 01:00:00', 'YYYY/MM/DD HH:MI:SS') 
AND d.Sched_Time < TO_DATE('2020-10-04 12:59:00', 'YYYY/MM/DD HH:MI:SS')