I need to optimize a MySQL query

General information for the business: Hotel manager with thousands of entries
Database management system (DBMS): MySQL
Description of requirements/functionality: I have a query , inside a Store Procedure, that is taking 8 seconds to pull data. I need it to pull data in less than a second.
The query takes this long, because it has too many sub-queries with JOIN LEFTs . I don't know the exact business rules, all I have is the query, inside a SP , and the tables from where this pulls data. I need a SP which give me the same result in less time.
Extra notes: #this is PART of the SP I need to optimize - I removed some similar subqueries to fit description size
BEGIN
select b.name, b.segment_id,
fn_roundocc(IFNULL(a.sunocc,0)) as sunocc, IFNULL(a.sunadr,0) as sunadr,
fn_roundocc(IFNULL(a.monocc,0)) as monocc, IFNULL(a.monadr,0) as monadr,
fn_roundocc(IFNULL(a.tueocc,0)) as tueocc, IFNULL(a.tueadr,0) as tueadr,
fn_roundocc(IFNULL(a.wedocc,0)) as wedocc, IFNULL(a.wedadr,0) as wedadr,
fn_roundocc(IFNULL(a.thuocc,0)) as thuocc, IFNULL(a.thuadr,0) as thuadr,
fn_roundocc(IFNULL(a.friocc,0)) as friocc, IFNULL(a.friadr,0) as friadr,
fn_roundocc(IFNULL(a.satocc,0)) as satocc, IFNULL(a.satadr,0) as satadr,
fn_roundocc(IFNULL(a.weekocc,0)) as weekocc, IFNULL(a.weekadr,0) as weekadr,
fn_roundocc(IFNULL(a.weekendocc,0)) as weekendocc, IFNULL(a.weekendadr,0) as weekendadr,
fn_roundocc(IFNULL(a.totalocc,0)) as totalocc, IFNULL(a.totaladr,0) as totaladr
from RM3Segments b
LEFT OUTER JOIN
(select segment_id,
(select IFNULL(sum(tot_occ)/count(S.statdate),0) From RM3TransientSegmentData S
left join RM3TransientData T on S.hotel_id=T.hotel_id and S.statdate=T.statdate
Where S.hotel_id = p_hotel_id AND S.statdate >= p_startdate AND S.statdate = p_startdate AND S.statdate = p_startdate AND S.statdate = p_startdate AND S.statdate = p_startdate AND S.statdate = p_startdate AND S.statdate = p_startdate AND S.statdate = p_startdate AND S.statdate = p_startdate AND statdate <= p_enddate GROUP BY a.segment_id ) as a
ON a.segment_id = b.segment_id
WHERE b.isactive=1 and b.istotal=0
and b.type='TM'
and b.customer_id = p_customer_id
ORDER BY b.sequence, b.name;
END