dplowman
asked on
Optimize query to run faster...
All,
I am trying to optimize the query below to execute faster. The current query takes around 2:30 minutes. I cannot seem to find out where the query is getting stuck. Any of you see where\how I can try to fix this?
Thanks,
Dustin
I am trying to optimize the query below to execute faster. The current query takes around 2:30 minutes. I cannot seem to find out where the query is getting stuck. Any of you see where\how I can try to fix this?
select a.FiscalMonth, a.fiscalweek, a.date, a.FULLNAME, a.CSR_LEVEL, a.hire_date, hoursworked, isnull(c.Faxes,0) as Faxes,
isnull(d.[PDL Originated],0) as 'PDL Originated', isnull(e.[ILP Originated],0) as 'ILP Originated',
isnull(f.[PDL Renewals],0) as 'PDL Renewals', isnull(f.[ILP Renewals],0) as 'ILP Renewals',
isnull(f.[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
--ALL DATES
(
SELECT distinct(a.fullname), fiscalmonth, fiscalweek, bda.DATE, hire_date, csr_level, department
FROM usonlinereporting.dbo.Busi<wbr ></wbr>ness_Dates<wbr ></wbr>_All bda, Associates$ a
WHERE bda.Date < GETDATE() - 1
and Day_of_week <> 1
and DEPARTMENT in ('loan servicing')
and bda.Date >= start_date and (bda.date <= end_date or end_date is null)
)a
left outer join
(
--KRONOS HOURS WORKED
SELECT fiscalmonth, FiscalWeek, bda.DATE, a.fullname, a.csr_level, a.HIRE_DATE, Sum(TimeHours)/COUNT(disti<wbr ></wbr>nct(a.EMP_<wbr ></wbr>CODE)) AS hoursworked
FROM usonlinereporting.dbo.Busi<wbr ></wbr>ness_Dates<wbr ></wbr>_All bda
INNER JOIN EBGOperationalReporting.db<wbr ></wbr>o.Kronos_H<wbr ></wbr>ours_EBG k ON bda.Date = k.date
INNER JOIN Associates$ a ON a.kronos_name = k.personfullname
WHERE paycodename = 'total worked hours'
and DEPARTMENT = 'loan servicing'
and k.Date >= start_date and (k.date <= end_date or end_date is null)
GROUP BY fiscalmonth, fiscalweek, bda.date, a.FULLNAME, a.CSR_LEVEL, a.HIRE_DATE
)b
on a.date = b.date and a.fullname = b.fullname
left join
(
--UNIQUE FAXES
select fiscalmonth, bda.date, fullname, count(distinct(bfd.fax_doc<wbr ></wbr>_id)) as Faxes
from usonlinereporting.dbo.bo_a<wbr ></wbr>pp_fax_doc<wbr ></wbr>s baf
inner join usonlinereporting.dbo.bo_f<wbr ></wbr>ax_docs bfd on bfd.FAX_DOC_ID = baf.FAX_DOC_ID
inner join usonlinereporting.dbo.busi<wbr ></wbr>ness_dates<wbr ></wbr>_all bda on bda.Date = dateadd(dd,0,datediff(dd,0<wbr ></wbr>,bfd.creat<wbr ></wbr>ed_date))
inner join Associates$ a on a.EMP_CODE = bfd.CREATED_BY
group by fiscalmonth, fiscalweek, bda.date, fullname
)c
on a.date = c.date and a.fullname = c.fullname
left join
(
--PDL STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, fiscalweek, bda.date, fullname,
count(distinct(app_no)) as 'PDL Originated'
from usonlinereporting.dbo.bo_i<wbr ></wbr>n_app_queu<wbr ></wbr>e bia
left join usonlinereporting.dbo.st_l<wbr ></wbr>o_master slm on bia.app_no = slm.loan_code
left join usonlinereporting.dbo.busi<wbr ></wbr>ness_dates<wbr ></wbr>_all bda on dateadd(dd,0,datediff(dd,0<wbr ></wbr>,bia.loan_<wbr ></wbr>originated<wbr ></wbr>)) = date
left join Associates$ a on a.emp_code = bia.originated_by
where LOAN_TYPE = 'pdl'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)d
on a.date = d.date and a.fullname = d.fullname
left join
(
--DILP STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, FiscalWeek, bda.date, fullname,
count(distinct(app_no)) as 'ILP Originated'
from usonlinereporting.dbo.bo_i<wbr ></wbr>n_app_queu<wbr ></wbr>e bia
left join usonlinereporting.dbo.st_i<wbr ></wbr>l_master slm on bia.app_no = slm.iloan_code
left join usonlinereporting.dbo.busi<wbr ></wbr>ness_dates<wbr ></wbr>_all bda on dateadd(dd,0,datediff(dd,0<wbr ></wbr>,bia.loan_<wbr ></wbr>originated<wbr ></wbr>)) = date
left join Associates$ a on a.emp_code = bia.originated_by
where bia.LOAN_TYPE = 'ilp'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)e
on a.date = e.date and a.fullname = e.fullname
left join
(
--Initiate and Originated Renewals by Emp_Code
select FiscalMonth, fiscalweek, 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(distinct(ren.app_no)<wbr ></wbr>) as 'Total Renewals'
from
(
select FiscalMonth, fiscalweek, date, loan_type, app_no, PROCESSING_STATUS, PREV_LOAN_NO
from usonlinereporting.dbo.bo_i<wbr ></wbr>n_app_queu<wbr ></wbr>e bia
inner join USOnlineReporting.dbo.Busi<wbr ></wbr>ness_Dates<wbr ></wbr>_All bda on bda.Date = dateadd(day,0,datediff(dd,<wbr ></wbr>0,bia.LOAN<wbr ></wbr>_ORIGINATE<wbr ></wbr>D))
where APP_TYPE = 'ren'
and PROCESSING_STATUS = 'originated'
)ren
inner join
(
select distinct(bn.app_no), a.fullname
from usonlinereporting.dbo.bo_i<wbr ></wbr>n_app_queu<wbr ></wbr>e bia
inner join usonlinereporting.dbo.bo_n<wbr ></wbr>otes 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.Busi<wbr ></wbr>ness_Dates<wbr ></wbr>_All bda on bda.Date = dateadd(day,0,datediff(dd,<wbr ></wbr>0,bn.DATE_<wbr ></wbr>CREATED))
where notes = 'Initiate Renewal button on the Courtesy Call'
)prev
on ren.PREV_LOAN_NO = prev.app_no
group by ren.fiscalmonth, fiscalweek, ren.date, prev.fullname
)f
on a.Date = f.Date and a.FULLNAME = f.fullname
left join
(
--Noble Inbound AHT
select fiscalmonth, FiscalWeek, 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.busi<wbr ></wbr>ness_dates<wbr ></wbr>_all bda on bda.Date = call_date
where appl = 'lsin'
group by fiscalmonth, fiscalweek, date, fullname
)g
on a.Date = g.Date and a.FULLNAME = g.FULLNAME
left join
(
--Noble Outbound AHT
select fiscalmonth, fiscalweek, 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.busi<wbr ></wbr>ness_dates<wbr ></wbr>_all bda on bda.Date = call_date
where appl <> 'lsin'
group by fiscalmonth, fiscalweek, date, fullname
)h
on a.Date = h.Date and a.FULLNAME = h.FULLNAME
where a.FiscalMonth = '201203'
order by a.fullname, a.DateThanks,
Dustin
Also can you post the query withoutb the garbage like:
...<wbr ></wbr>....<wbr ></wbr>_All....
First tought is to try use CTE in SQL but would need the clean query to provide a re-write with CTE besides of what I posted above.
...<wbr ></wbr>....<wbr ></wbr>_All....
First tought is to try use CTE in SQL but would need the clean query to provide a re-write with CTE besides of what I posted above.
ASKER
Sorry, not sure why the code contained the <wbr>,
select a.FiscalMonth, a.fiscalweek, a.date, a.FULLNAME, a.CSR_LEVEL, a.hire_date, hoursworked, isnull(c.Faxes,0) as Faxes,
isnull(d.[PDL Originated],0) as 'PDL Originated', isnull(e.[ILP Originated],0) as 'ILP Originated',
isnull(f.[PDL Renewals],0) as 'PDL Renewals', isnull(f.[ILP Renewals],0) as 'ILP Renewals',
isnull(f.[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
--ALL DATES
(
SELECT distinct(a.fullname), fiscalmonth, fiscalweek, bda.DATE, hire_date, csr_level, department
FROM usonlinereporting.dbo.Business_Dates_All bda, Associates$ a
WHERE bda.Date < GETDATE() - 1
and Day_of_week <> 1
and DEPARTMENT in ('loan servicing')
and bda.Date >= start_date and (bda.date <= end_date or end_date is null)
)a
left outer join
(
--KRONOS HOURS WORKED
SELECT fiscalmonth, FiscalWeek, bda.DATE, a.fullname, a.csr_level, a.HIRE_DATE, 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
WHERE paycodename = 'total worked hours'
and DEPARTMENT = 'loan servicing'
and k.Date >= start_date and (k.date <= end_date or end_date is null)
GROUP BY fiscalmonth, fiscalweek, bda.date, a.FULLNAME, a.CSR_LEVEL, a.HIRE_DATE
)b
on a.date = b.date and a.fullname = b.fullname
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, fiscalweek, bda.date, fullname
)c
on a.date = c.date and a.fullname = c.fullname
left join
(
--PDL STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, fiscalweek, bda.date, fullname,
count(distinct(app_no)) as 'PDL 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.business_dates_all bda on dateadd(dd,0,datediff(dd,0,bia.loan_originated)) = date
left join Associates$ a on a.emp_code = bia.originated_by
where LOAN_TYPE = 'pdl'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)d
on a.date = d.date and a.fullname = d.fullname
left join
(
--DILP STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, FiscalWeek, bda.date, fullname,
count(distinct(app_no)) as 'ILP Originated'
from usonlinereporting.dbo.bo_in_app_queue bia
left join usonlinereporting.dbo.st_il_master slm on bia.app_no = slm.iloan_code
left join usonlinereporting.dbo.business_dates_all bda on dateadd(dd,0,datediff(dd,0,bia.loan_originated)) = date
left join Associates$ a on a.emp_code = bia.originated_by
where bia.LOAN_TYPE = 'ilp'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)e
on a.date = e.date and a.fullname = e.fullname
left join
(
--Initiate and Originated Renewals by Emp_Code
select FiscalMonth, fiscalweek, 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(distinct(ren.app_no)) as 'Total Renewals'
from
(
select FiscalMonth, fiscalweek, 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, fiscalweek, ren.date, prev.fullname
)f
on a.Date = f.Date and a.FULLNAME = f.fullname
left join
(
--Noble Inbound AHT
select fiscalmonth, FiscalWeek, 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, fiscalweek, date, fullname
)g
on a.Date = g.Date and a.FULLNAME = g.FULLNAME
left join
(
--Noble Outbound AHT
select fiscalmonth, fiscalweek, 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, fiscalweek, date, fullname
)h
on a.Date = h.Date and a.FULLNAME = h.FULLNAME
where a.FiscalMonth = @fiscalmonth
order by a.fullname, a.DateASKER
Not familiar with set showplan_text on. When I paste this code at the beginning of the query I am getting.... The Set Showplan statement must be the only statement in the batch.
try to move similar sub-queries outside into temp table, and use temp table in main query, like this....
hope this helps......
select fiscalmonth, fiscalweek, date, fullname, appl,
SUM(time_connect) as 'Connect',
SUM(time_waiting) as 'Waiting',
SUM(time_acw) as 'ACW',
SUM(time_paused) as 'Paused',
SUM(time_deassigned) as 'Deassign',
SUM(tot_calls) as 'Calls'
INTO #temp_Noble_Call_History
from noble_call_history n
inner join associates$ a on a.NOBLE_ID = n.tsr
inner join usonlinereporting.dbo.busi
group by fiscalmonth, fiscalweek, date, fullname, appl
select a.FiscalMonth, a.fiscalweek, a.date, a.FULLNAME, a.CSR_LEVEL, a.hire_date, hoursworked, isnull(c.Faxes,0) as Faxes,
isnull(d.[PDL Originated],0) as 'PDL Originated', isnull(e.[ILP Originated],0) as 'ILP Originated',
isnull(f.[PDL Renewals],0) as 'PDL Renewals', isnull(f.[ILP Renewals],0) as 'ILP Renewals',
isnull(f.[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
--ALL DATES
(
SELECT distinct(a.fullname), fiscalmonth, fiscalweek, bda.DATE, hire_date, csr_level, department
FROM usonlinereporting.dbo.Busi
WHERE bda.Date < GETDATE() - 1
and Day_of_week <> 1
and DEPARTMENT in ('loan servicing')
and bda.Date >= start_date and (bda.date <= end_date or end_date is null)
)a
left outer join
(
--KRONOS HOURS WORKED
SELECT fiscalmonth, FiscalWeek, bda.DATE, a.fullname, a.csr_level, a.HIRE_DATE, Sum(TimeHours)/COUNT(disti
FROM usonlinereporting.dbo.Busi
INNER JOIN EBGOperationalReporting.db
INNER JOIN Associates$ a ON a.kronos_name = k.personfullname
WHERE paycodename = 'total worked hours'
and DEPARTMENT = 'loan servicing'
and k.Date >= start_date and (k.date <= end_date or end_date is null)
GROUP BY fiscalmonth, fiscalweek, bda.date, a.FULLNAME, a.CSR_LEVEL, a.HIRE_DATE
)b
on a.date = b.date and a.fullname = b.fullname
left join
(
--UNIQUE FAXES
select fiscalmonth, bda.date, fullname, count(distinct(bfd.fax_doc
from usonlinereporting.dbo.bo_a
inner join usonlinereporting.dbo.bo_f
inner join usonlinereporting.dbo.busi
inner join Associates$ a on a.EMP_CODE = bfd.CREATED_BY
group by fiscalmonth, fiscalweek, bda.date, fullname
)c
on a.date = c.date and a.fullname = c.fullname
left join
(
--PDL STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, fiscalweek, bda.date, fullname,
count(distinct(app_no)) as 'PDL Originated'
from usonlinereporting.dbo.bo_i
left join usonlinereporting.dbo.st_l
left join usonlinereporting.dbo.busi
left join Associates$ a on a.emp_code = bia.originated_by
where LOAN_TYPE = 'pdl'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)d
on a.date = d.date and a.fullname = d.fullname
left join
(
--DILP STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, FiscalWeek, bda.date, fullname,
count(distinct(app_no)) as 'ILP Originated'
from usonlinereporting.dbo.bo_i
left join usonlinereporting.dbo.st_i
left join usonlinereporting.dbo.busi
left join Associates$ a on a.emp_code = bia.originated_by
where bia.LOAN_TYPE = 'ilp'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)e
on a.date = e.date and a.fullname = e.fullname
left join
(
--Initiate and Originated Renewals by Emp_Code
select FiscalMonth, fiscalweek, 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(distinct(ren.app_no)
from
(
select FiscalMonth, fiscalweek, date, loan_type, app_no, PROCESSING_STATUS, PREV_LOAN_NO
from usonlinereporting.dbo.bo_i
inner join USOnlineReporting.dbo.Busi
where APP_TYPE = 'ren'
and PROCESSING_STATUS = 'originated'
)ren
inner join
(
select distinct(bn.app_no), a.fullname
from usonlinereporting.dbo.bo_i
inner join usonlinereporting.dbo.bo_n
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.Busi
where notes = 'Initiate Renewal button on the Courtesy Call'
)prev
on ren.PREV_LOAN_NO = prev.app_no
group by ren.fiscalmonth, fiscalweek, ren.date, prev.fullname
)f
on a.Date = f.Date and a.FULLNAME = f.fullname
left join
(
--Noble Inbound AHT
select fiscalmonth, FiscalWeek, date, fullname,
Connect as 'Inbound Connect',
Waiting as 'Inbound Waiting',
ACW as 'Inbound ACW',
Paused as 'Inbound Paused',
Deassign as 'Inbound Deassign',
Calls as 'Inbound Calls'
from #temp_Noble_Call_History
where appl = 'lsin'
)g
on a.Date = g.Date and a.FULLNAME = g.FULLNAME
left join
(
--Noble Outbound AHT
select fiscalmonth, fiscalweek, date, fullname,
Connect as 'Outbound Connect',
Waiting as 'Outbound Waiting',
ACW as 'Outbound ACW',
Paused as 'Outbound Paused',
Deassign as 'Outbound Deassign',
Calls as 'Outbound Calls'
from #temp_Noble_Call_History
where appl <> 'lsin'
)h
on a.Date = h.Date and a.FULLNAME = h.FULLNAME
where a.FiscalMonth = @fiscalmonth
order by a.fullname, a.Date
DROP TABLE #temp_Noble_Call_History
hope this helps......
select fiscalmonth, fiscalweek, date, fullname, appl,
SUM(time_connect) as 'Connect',
SUM(time_waiting) as 'Waiting',
SUM(time_acw) as 'ACW',
SUM(time_paused) as 'Paused',
SUM(time_deassigned) as 'Deassign',
SUM(tot_calls) as 'Calls'
INTO #temp_Noble_Call_History
from noble_call_history n
inner join associates$ a on a.NOBLE_ID = n.tsr
inner join usonlinereporting.dbo.busi
group by fiscalmonth, fiscalweek, date, fullname, appl
select a.FiscalMonth, a.fiscalweek, a.date, a.FULLNAME, a.CSR_LEVEL, a.hire_date, hoursworked, isnull(c.Faxes,0) as Faxes,
isnull(d.[PDL Originated],0) as 'PDL Originated', isnull(e.[ILP Originated],0) as 'ILP Originated',
isnull(f.[PDL Renewals],0) as 'PDL Renewals', isnull(f.[ILP Renewals],0) as 'ILP Renewals',
isnull(f.[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
--ALL DATES
(
SELECT distinct(a.fullname), fiscalmonth, fiscalweek, bda.DATE, hire_date, csr_level, department
FROM usonlinereporting.dbo.Busi
WHERE bda.Date < GETDATE() - 1
and Day_of_week <> 1
and DEPARTMENT in ('loan servicing')
and bda.Date >= start_date and (bda.date <= end_date or end_date is null)
)a
left outer join
(
--KRONOS HOURS WORKED
SELECT fiscalmonth, FiscalWeek, bda.DATE, a.fullname, a.csr_level, a.HIRE_DATE, Sum(TimeHours)/COUNT(disti
FROM usonlinereporting.dbo.Busi
INNER JOIN EBGOperationalReporting.db
INNER JOIN Associates$ a ON a.kronos_name = k.personfullname
WHERE paycodename = 'total worked hours'
and DEPARTMENT = 'loan servicing'
and k.Date >= start_date and (k.date <= end_date or end_date is null)
GROUP BY fiscalmonth, fiscalweek, bda.date, a.FULLNAME, a.CSR_LEVEL, a.HIRE_DATE
)b
on a.date = b.date and a.fullname = b.fullname
left join
(
--UNIQUE FAXES
select fiscalmonth, bda.date, fullname, count(distinct(bfd.fax_doc
from usonlinereporting.dbo.bo_a
inner join usonlinereporting.dbo.bo_f
inner join usonlinereporting.dbo.busi
inner join Associates$ a on a.EMP_CODE = bfd.CREATED_BY
group by fiscalmonth, fiscalweek, bda.date, fullname
)c
on a.date = c.date and a.fullname = c.fullname
left join
(
--PDL STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, fiscalweek, bda.date, fullname,
count(distinct(app_no)) as 'PDL Originated'
from usonlinereporting.dbo.bo_i
left join usonlinereporting.dbo.st_l
left join usonlinereporting.dbo.busi
left join Associates$ a on a.emp_code = bia.originated_by
where LOAN_TYPE = 'pdl'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)d
on a.date = d.date and a.fullname = d.fullname
left join
(
--DILP STANDARD ORIGINATIONS BY EMP_CODE
select fiscalmonth, FiscalWeek, bda.date, fullname,
count(distinct(app_no)) as 'ILP Originated'
from usonlinereporting.dbo.bo_i
left join usonlinereporting.dbo.st_i
left join usonlinereporting.dbo.busi
left join Associates$ a on a.emp_code = bia.originated_by
where bia.LOAN_TYPE = 'ilp'
and bia.app_type = 'std'
and PROCESSING_STATUS = 'originated'
and LOAN_STATUS_ID <> 'v'
group by fiscalmonth, fiscalweek, bda.date, fullname
)e
on a.date = e.date and a.fullname = e.fullname
left join
(
--Initiate and Originated Renewals by Emp_Code
select FiscalMonth, fiscalweek, 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(distinct(ren.app_no)
from
(
select FiscalMonth, fiscalweek, date, loan_type, app_no, PROCESSING_STATUS, PREV_LOAN_NO
from usonlinereporting.dbo.bo_i
inner join USOnlineReporting.dbo.Busi
where APP_TYPE = 'ren'
and PROCESSING_STATUS = 'originated'
)ren
inner join
(
select distinct(bn.app_no), a.fullname
from usonlinereporting.dbo.bo_i
inner join usonlinereporting.dbo.bo_n
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.Busi
where notes = 'Initiate Renewal button on the Courtesy Call'
)prev
on ren.PREV_LOAN_NO = prev.app_no
group by ren.fiscalmonth, fiscalweek, ren.date, prev.fullname
)f
on a.Date = f.Date and a.FULLNAME = f.fullname
left join
(
--Noble Inbound AHT
select fiscalmonth, FiscalWeek, date, fullname,
Connect as 'Inbound Connect',
Waiting as 'Inbound Waiting',
ACW as 'Inbound ACW',
Paused as 'Inbound Paused',
Deassign as 'Inbound Deassign',
Calls as 'Inbound Calls'
from #temp_Noble_Call_History
where appl = 'lsin'
)g
on a.Date = g.Date and a.FULLNAME = g.FULLNAME
left join
(
--Noble Outbound AHT
select fiscalmonth, fiscalweek, date, fullname,
Connect as 'Outbound Connect',
Waiting as 'Outbound Waiting',
ACW as 'Outbound ACW',
Paused as 'Outbound Paused',
Deassign as 'Outbound Deassign',
Calls as 'Outbound Calls'
from #temp_Noble_Call_History
where appl <> 'lsin'
)h
on a.Date = h.Date and a.FULLNAME = h.FULLNAME
where a.FiscalMonth = @fiscalmonth
order by a.fullname, a.Date
DROP TABLE #temp_Noble_Call_History
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you post the graphical plan or run the command below before rerun thsi query and post the text plan here:
set showplan_text on;
set statistics io on;
set statistics time on;