Query taking forever to execute, any advice?

select a.FiscalMonth, a.date, a.FULLNAME, hoursworked, isnull(b.Faxes,0) as Faxes,
 isnull(c.[PDL Originated],0) as 'PDL Originated', isnull(c.[ILP Originated],0) as 'ILP Originated', 
 isnull(d.[PDL Renewals],0) as 'PDL Renewals', isnull(d.[ILP Renewals],0) as 'ILP Renewals',
 isnull(d.[Total Renewals],0) as 'Total Renewals',
 isnull([Inbound Connect],0) as 'Inbound Connect',
 isnull([Inbound Waiting],0) as 'Inbound Waiting',
 isnull([Inbound ACW],0) as 'Inbound ACW',
 isnull([Inbound Paused],0) as 'Inbound Paused',
 isnull([Inbound Deassign],0) as 'Inbound Deassign',
 isnull([Inbound Calls],0) as 'Inbound Calls',
 isnull([outbound Connect],0) as 'outbound Connect',
 isnull([outbound Waiting],0) as 'outbound Waiting',
 isnull([outbound ACW],0) as 'outbound ACW',
 isnull([outbound Paused],0) as 'outbound Paused',
 isnull([outbound Deassign],0) as 'outbound Deassign',
 isnull([outbound Calls],0) as 'outbound Calls'
from

(
--KRONOS HOURS WORKED
SELECT fiscalmonth, bda.DATE, a.fullname, Sum(TimeHours)/COUNT(distinct(a.EMP_CODE)) AS hoursworked
FROM usonlinereporting.dbo.Business_Dates_All bda 
INNER JOIN EBGOperationalReporting.dbo.Kronos_Hours_EBG k ON bda.Date = k.date
INNER JOIN Associates$ a ON a.kronos_name = k.personfullname
inner join TeamLeads$ t on t.FULLNAME = a.TEAM_LEAD 
WHERE paycodename = 'total worked hours'
and DEPARTMENT = 'loan servicing'
and a.STATUS = 'a'
and k.Date >= start_date and (k.date <= end_date or end_date is null)
GROUP BY fiscalmonth, bda.date, a.FULLNAME
)a

left join
(
--UNIQUE FAXES
select fiscalmonth, bda.date, fullname, count(distinct(bfd.fax_doc_id)) as Faxes
from usonlinereporting.dbo.bo_app_fax_docs baf
inner join usonlinereporting.dbo.bo_fax_docs bfd on bfd.FAX_DOC_ID = baf.FAX_DOC_ID
inner join usonlinereporting.dbo.business_dates_all bda on bda.Date = dateadd(dd,0,datediff(dd,0,bfd.created_date))
inner join Associates$ a on a.EMP_CODE = bfd.CREATED_BY
group by fiscalmonth, bda.date, fullname
)b

on a.date = b.date and a.fullname = b.fullname

left join
(
--PDL AND DILP STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, bda.date, fullname, 
SUM(case when bia.loan_type = 'pdl' and processing_status = 'originated' then 1 else 0 end)-SUM(case when slm.loan_status_id = 'v' then 1 else 0 end) as 'PDL Originated',
SUM(case when bia.loan_type = 'ilp' and processing_status = 'originated' then 1 else 0 end)-SUM(case when sim.loan_status_id = 'v' then 1 else 0 end) as 'ILP Originated',
SUM(case when processing_status = 'originated' then 1 else 0 end)-SUM(case when slm.loan_status_id = 'v' then 1 else 0 end)-SUM(case when sim.loan_status_id = 'v' then 1 else 0 end) as 'Total Originated'
from usonlinereporting.dbo.bo_in_app_queue bia
left join usonlinereporting.dbo.st_lo_master slm on bia.app_no = slm.loan_code
left join usonlinereporting.dbo.st_il_master sim on bia.app_no = sim.iloan_code
inner join usonlinereporting.dbo.business_dates_all bda on dateadd(dd,0,datediff(dd,0,bia.loan_originated)) = date
inner join Associates$ a on a.emp_code = bia.originated_by
where bia.app_type = 'std'
group by fiscalmonth, bda.date, fullname
)c

on a.date = c.date and a.fullname = c.fullname

left join
(
--Initiate and Originated Renewals by Emp_Code
select FiscalMonth, ren.DATE, prev.FULLNAME, 
sum(case when loan_type = 'pdl' then 1 else 0 end) as 'PDL Renewals',
sum(case when loan_type = 'ilp' then 1 else 0 end) as 'ILP Renewals',
COUNT(ren.app_no) as 'Total Renewals'

from

	(
	select FiscalMonth, date, loan_type, app_no, PROCESSING_STATUS, PREV_LOAN_NO
	from usonlinereporting.dbo.bo_in_app_queue bia
	inner join USOnlineReporting.dbo.Business_Dates_All bda on bda.Date = dateadd(day,0,datediff(dd,0,bia.LOAN_ORIGINATED))
	where APP_TYPE = 'ren'
	and PROCESSING_STATUS = 'originated'
	)ren

	inner join
	(
	select distinct(bn.app_no), a.fullname
	from usonlinereporting.dbo.bo_in_app_queue bia
	inner join usonlinereporting.dbo.bo_notes bn  on bn.APP_NO = bia.APP_NO
	inner join Associates$ a on a.EMP_CODE = bn.CREATED_BY
	inner join TeamLeads$ tl on tl.FULLNAME = a.team_lead
	--inner join USOnlineReporting.dbo.Business_Dates_All bda on bda.Date = dateadd(day,0,datediff(dd,0,bn.DATE_CREATED))
	where notes = 'Initiate Renewal button on the Courtesy Call'
	)prev

	on ren.PREV_LOAN_NO = prev.app_no

	group by ren.fiscalmonth, ren.date, prev.fullname
)d

on a.Date = d.Date and a.FULLNAME = d.fullname

left join
(
--Noble Inbound AHT
select fiscalmonth, date, fullname,
SUM(time_connect) as 'Inbound Connect',
SUM(time_waiting) as 'Inbound Waiting',
SUM(time_acw) as 'Inbound ACW',
SUM(time_paused) as 'Inbound Paused',
SUM(time_deassigned) as 'Inbound Deassign',
SUM(tot_calls) as 'Inbound Calls'
from noble_call_history n 
inner join associates$ a on a.NOBLE_ID = n.tsr
inner join usonlinereporting.dbo.business_dates_all bda on bda.Date = call_date
where appl = 'lsin'
group by fiscalmonth, date, fullname
)e

on a.Date = e.Date and a.FULLNAME = e.FULLNAME

left join
(
--Noble Outbound AHT
select fiscalmonth, date, fullname,
SUM(time_connect) as 'Outbound Connect',
SUM(time_waiting) as 'Outbound Waiting',
SUM(time_acw) as 'Outbound ACW',
SUM(time_paused) as 'Outbound Paused',
SUM(time_deassigned) as 'Outbound Deassign',
SUM(tot_calls) as 'Outbound Calls'
from noble_call_history n 
inner join associates$ a on a.NOBLE_ID = n.tsr
inner join usonlinereporting.dbo.business_dates_all bda on bda.Date = call_date
where appl <> 'lsin'
group by fiscalmonth, date, fullname
)f

on a.Date = f.Date and a.FULLNAME = f.FULLNAME

where a.FiscalMonth = '201110'
order by a.fullname, a.Date

Select all Open in new window