quote:

---

Originally posted by rmg1

Hi all

<snip>

Anyone any ideas?

---

declare @start_date date
declare @end_date date
set @start_date = '2010-11-01'  --Alter this date as required
set @end_date = '2010-11-01'    --Alter this date as required
--set @start_date=(GETDATE()-5-datepart(dw,getdate()))
--set @end_date=(GETDATE()+1-datepart(dw,getdate()))
-----------------------------------------------------------
use SymposiumDW
if exists(select * from tempdb.dbo.sysobjects where name like '%#logged_in%')
drop table #logged_in
if exists(select * from tempdb.dbo.sysobjects where name like '%#phone_times%')
drop table #phone_times
if exists(select * from tempdb.dbo.sysobjects where name like '%#phone_times_2%')
drop table #phone_times_2
--------------------------- Get talk/hold/wrap/etc for master table -----------------------------------
select
	agent.FirstName + agent.Surname + agent.SkillsetName + cast(stat.Timestamp as varchar) as Unique_ID,
	agent.SkillsetName as Skill,
	CONVERT(varchar(10),stat.Timestamp,103) as [Date],
	cast(stat.Timestamp as time(0)) as [Time],
	isnull(stat.LoggedInTime,0) as [Logged_In],
	isnull(stat.TalkTime,0) - isnull(stat.HoldTime,0) as [Talk_Time],
	isnull(stat.HoldTime,0) - isnull(stat.WalkawayTime,0) as [Hold_Time],
	isnull(stat.WaitingTime,0) - isnull(stat.ReservedTime,0) as [Wait_Time],
	isnull(stat.WalkawayTime,0) as WalkAway,
	isnull(stat.RingTime,0) as Ring_Time,
	isnull(stat.BreakTime,0) as [Break_Time],
	isnull(stat.BusyMiscTime,0) as [Busy_Misc_Time],
	isnull(stat.BusyOnDNTime,0) as [Busy_on_DN_Time],
	isnull(stat.ConsultationTime,0) as [Consultation_Time],
	isnull(stat.ReservedTime,0) as [Reserved_Time],
	isnull(stat.CallsAnswered,0) as CallsAnswered
	into #logged_in
from
	V_IRTAgentList agent
		join FACT_iAgentPerformanceStat stat on (agent.UserID = stat.UserID
													and agent.HUBID=stat.HUBID)
where
	cast(stat.Timestamp as DATE) between @start_date and @end_date
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
	and cast(stat.Timestamp as time(0)) between '09:00:00' and '17:00:00'
order by
	[Date],
	[Time],
	Skill
--------------------------- Get phone codes and times -----------------------------------
select
	agent.FirstName + agent.Surname + agent.SkillsetName + cast(codes.Timestamp as varchar) as Unique_ID,
	agent.SkillsetName as Skill,
	CONVERT(varchar(10),codes.Timestamp,103) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	isnull((case when (activity.ActivityCode='0') then codes.ActivityTime end),0) as [System_Default],
	isnull((case when (activity.ActivityCode='00') then codes.ActivityTime end),0) as [Skillset_Default],
	isnull((case when (activity.ActivityCode='000') then codes.ActivityTime end),0) as [Not_Ready_Default],
	isnull((case when (activity.ActivityCode='0000') then codes.ActivityTime end),0) as [Not_Ready_Pull_Default],
	isnull((case when (activity.ActivityCode='01') then codes.ActivityTime end),0) as [Breaks],
	isnull((case when (activity.ActivityCode='02') then codes.ActivityTime end),0) as [Comfort_Breaks],
	isnull((case when (activity.ActivityCode='03') then codes.ActivityTime end),0) as [Training],
	isnull((case when (activity.ActivityCode='04') then codes.ActivityTime end),0) as [Meetings],
	isnull((case when (activity.ActivityCode='05') then codes.ActivityTime end),0) as [Reflection],
	isnull((case when (activity.ActivityCode='06') then codes.ActivityTime end),0) as [Other],
	isnull((case when (activity.ActivityCode='07') then codes.ActivityTime end),0) as [System_Failure],
	isnull((case when (activity.ActivityCode='08') then codes.ActivityTime end),0) as [Wrap_Time],
	isnull((case when (activity.ActivityCode='09') then codes.ActivityTime end),0) as [Research],
	isnull((case when (activity.ActivityCode='10') then codes.ActivityTime end),0) as [Postal_Work],
	isnull((case when (activity.ActivityCode='11') then codes.ActivityTime end),0) as [Follow_Up],
	isnull((case when (activity.ActivityCode='12') then codes.ActivityTime end),0) as [Child_Protection],
	isnull((case when (activity.ActivityCode='13') then codes.ActivityTime end),0) as [Vulnerable_Adult],
	isnull((case when (activity.ActivityCode='14') then codes.ActivityTime end),0) as [Rule_of_Thumb],
	isnull((case when (activity.ActivityCode='15') then codes.ActivityTime end),0) as [TAL_Activity],
	isnull((case when (activity.ActivityCode='22') then codes.ActivityTime end),0) as [Nurse_Assessment],
	isnull((case when (activity.ActivityCode not in ('0','00','000','0000','01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','22')) then codes.ActivityTime end),0) as [Incorrect_Code]
	into #phone_times
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on (agent.UserID=codes.UserID
										and agent.HUBID=codes.HUBID)
		join DIM_Activity activity on (codes.ActivityCode=activity.ActivityCode
										and codes.HUBID=activity.HUBID)
		
where
	cast(codes.Timestamp as DATE) between @start_date and @end_date
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
	and cast(codes.Timestamp as time(0)) between '09:00:00' and '17:00:00'
order by
	[Date],
	[Time],
	Skill