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
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.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE