We help IT Professionals succeed at work.
Get Started

Query taking forever to execute, any advice?

207 Views
Last Modified: 2012-08-14
Is there anyway to speed up the performance of this query.
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

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2012
Commented:
This problem has been solved!
Unlock 1 Answer and 10 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE