Please help refine this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Please help refine this query

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-10-19 : 06:00:22

Hi all

I've got a fairly sizeable set of select statments which are going into temporary tables (can't create views due to permissions issues).

Creating the temp tables takes about 8 minutes but getting all the temp tables into one big table seems to take far longer than it should (I've run it overnight and it didn't finish!).

Can someone please have a look at the query and see if I'm going wrong somewhere?


declare @start_date date
declare @end_date date
set @start_date = '2010-10-04'  --Alter this date as required
set @end_date = '2010-10-10'    --Alter this date as required
-----------------------------------------------------------
use SymposiumDW
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable1%')
drop table #temptable1
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable2%')
drop table #temptable2
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable3%')
drop table #temptable3
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable4%')
drop table #temptable4
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable5%')
drop table #temptable5
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable6%')
drop table #temptable6
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable7%')
drop table #temptable7
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable8%')
drop table #temptable8
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable9%')
drop table #temptable9
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable10%')
drop table #temptable10
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable11%')
drop table #temptable11
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable12%')
drop table #temptable12
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable13%')
drop table #temptable13
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable14%')
drop table #temptable14
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable15%')
drop table #temptable15
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable16%')
drop table #temptable16
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable17%')
drop table #temptable17
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable18%')
drop table #temptable18
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable19%')
drop table #temptable19
if exists(select * from tempdb.dbo.sysobjects where name like '%#temptable20%')
drop table #temptable20
--------------------------- Get phone codes and times -----------------------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Skillset_Default]
	into #temptable1
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode ='00'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
------------------------- get Not Ready Default ------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Not_Ready_Default]
	into #temptable2
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '000'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
---------------------- get Not Ready Pull Default ---------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Not_Rdy_Pull_Default]
	into #temptable3
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '0000'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
-------------------- get Breaks -----------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Breaks]
	into #temptable4
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '01'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
------------------ get Comfort Breaks -------------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Comfort Break]
	into #temptable5
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '02'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
------------------ get Training-------------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Training]
	into #temptable6
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '03'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
-------------------- get Meetings -----------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Meetings]
	into #temptable7
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '04'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
---------------------- get Reflection ---------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Reflection]
	into #temptable8
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '05'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
-------------------- get Other -----------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Other]
	into #temptable9
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '06'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
--------------------get System Failure -----------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [System Failure]
	into #temptable10
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '07'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
---------------------- get Wrap Time ---------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Wrap Time]
	into #temptable11
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '08'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
---------------------- get Research ---------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Research]
	into #temptable12
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '09'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
---------------------- get Postal Work ---------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Postal Work]
	into #temptable13
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '10'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
------------------------ get Follow Up -------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Follow Up]
	into #temptable14
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '11'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
----------------------- get Child Protection --------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Child Protection]
	into #temptable15
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '12'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
------------------------ get Vulnerable Adult -------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Vulnerable Adults]
	into #temptable16
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '13'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
------------------------ get Rule of Thumb -------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Rule of Thumb]
	into #temptable17
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '14'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
------------------------ get TAL Activity -------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [TAL Activity]
	into #temptable18
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '15'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
----------------------- get Nurse Assessment --------------------
select
	agent.SkillsetName as Skill,
	cast(codes.Timestamp as DATE) as [Date],
	cast(codes.Timestamp as time(0)) as [Time],
	ActivityTime as [Nurse Assessment]
	into #temptable19
from
	FACT_iActivityCodeStat codes
		join V_IRTAgentList agent on agent.UserID=codes.UserID
		join DIM_Activity activity on codes.ActivityCode=activity.ActivityCode
where
	cast(codes.Timestamp as DATE) >= @start_date
	and cast(codes.Timestamp as DATE) <= @end_date
	and activity.ActivityCode = '22'
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
--------------------------- Get talk/hold/wrap/etc for master table -----------------------------------
select
	agent.SkillsetName as Skill,
	cast(stat.Timestamp as DATE) as [Date],
	cast(stat.Timestamp as time(0)) as [Time],
	stat.LoggedInTime as [Logged In],
	stat.TalkTime as [Talk Time],  -- Talk + Wait time is System_default time
	stat.HoldTime as [Hold Time],
	stat.WaitingTime as [Wait Time],
	stat.WalkawayTime as WalkAway,
	stat.RingTime as RingTime,
	stat.CallsAnswered
	into #temptable20
from
	V_IRTAgentList agent
		join FACT_iAgentPerformanceStat stat on agent.UserID = stat.UserID
where
	cast(stat.Timestamp as DATE) >= @start_date
	and cast(stat.Timestamp as DATE) <= @end_date
	and agent.SkillsetName in ('Health Advisor','Nurse Advisor','Dental Nurse','HI Advisor')
----------------------  Amalgamate into one table ----------------------------------------
select
	t20.*,
	(t20.[Talk Time] + t20.[Hold Time] + t20.[Wait Time] + isnull(t11.[Wrap Time],0) + t20.WalkAway)/t20.CallsAnswered as AHT,
	isnull(t1.Skillset_Default,0) as [Skillset Default],
	isnull(t2.Not_Ready_Default,0) as Not_Ready_Default,
	isnull(t3.Not_Rdy_Pull_Default,0) as Not_Rdy_Pull_Default,
	isnull(t4.Breaks,0) as Breaks,
	isnull(t5.[Comfort Break],0) as[Comfort Break],
	isnull(t6.Training,0) as Training,
	isnull(t7.Meetings,0) as Meetings,
	isnull(t8.Reflection,0) as Reflection,
	isnull(t9.Other,0) as Other,
	isnull(t10.[System Failure],0) as [System Failure],
	isnull(t11.[Wrap Time],0) as [Wrap Time],
	isnull(t12.Research,0) as Research,
	isnull(t13.[Postal Work],0) as [Postal Work],
	isnull(t14.[Follow Up],0) as [Follow Up],
	isnull(t15.[Child Protection],0) as [Child Protection],
	isnull(t16.[Vulnerable Adults],0) as [Vulnerable Adults],
	isnull(t17.[Rule of Thumb],0) as [Rule of Thumb],
	isnull(t18.[TAL Activity],0) as [TAL Activity],
	isnull(t19.[Nurse Assessment],0) as [Nurse Assessment]
from
	#temptable20 t20
		left join #temptable1 t1 on
			(t1.Date=t20.Date
			and t1.Time=t20.Time
			and t1.Skill=t20.Skill)
		left join #temptable2 t2 on 
			(t2.Date=t20.Date
			and t2.Time=t20.Time
			and t2.Skill=t20.Skill)
		left join #temptable3 t3 on 
			(t3.Date=t20.Date
			and t3.Time=t20.Time
			and t3.Skill=t20.Skill)
		left join #temptable4 t4 on 
			(t4.Date=t20.Date
			and t4.Time=t20.Time
			and t4.Skill=t20.Skill)
		left join #temptable5 t5 on 
			(t5.Date=t20.Date
			and t5.Time=t20.Time
			and t5.Skill=t20.Skill)
		left join #temptable6 t6 on 
			(t6.Date=t20.Date
			and t6.Time=t20.Time
			and t6.Skill=t20.Skill)
		left join #temptable7 t7 on 
			(t7.Date=t20.Date
			and t7.Time=t20.Time
			and t7.Skill=t20.Skill)
		left join #temptable8 t8 on 
			(t8.Date=t20.Date
			and t8.Time=t20.Time
			and t8.Skill=t20.Skill)
		left join #temptable9 t9 on 
			(t9.Date=t20.Date
			and t9.Time=t20.Time
			and t9.Skill=t20.Skill)
		left join #temptable10 t10 on 
			(t10.Date=t20.Date
			and t10.Time=t20.Time
			and t10.Skill=t20.Skill)
		left join #temptable11 t11 on 
			(t11.Date=t20.Date
			and t11.Time=t20.Time
			and t11.Skill=t20.Skill)
		left join #temptable12 t12 on 
			(t12.Date=t20.Date
			and t12.Time=t20.Time
			and t12.Skill=t20.Skill)
		left join #temptable13 t13 on 
			(t13.Date=t20.Date
			and t13.Time=t20.Time
			and t13.Skill=t20.Skill)
		left join #temptable14 t14 on 
			(t14.Date=t20.Date
			and t14.Time=t20.Time
			and t14.Skill=t20.Skill)
		left join #temptable15 t15 on 
			(t15.Date=t20.Date
			and t15.Time=t20.Time
			and t15.Skill=t20.Skill)
		left join #temptable16 t16 on 
			(t16.Date=t20.Date
			and t16.Time=t20.Time
			and t16.Skill=t20.Skill)
		left join #temptable17 t17 on 
			(t17.Date=t20.Date
			and t17.Time=t20.Time
			and t17.Skill=t20.Skill)
		left join #temptable18 t18 on 
			(t18.Date=t20.Date
			and t18.Time=t20.Time
			and t18.Skill=t20.Skill)
		left join #temptable19 t19 on 
			(t19.Date=t20.Date
			and t19.Time=t20.Time
			and t19.Skill=t20.Skill)
group by
	t20.Date,
	t20.Time,
	t20.Skill,
	t20.[Logged In],
	t20.[Talk Time],
	t20.[Hold Time],
	t20.[Wait Time],
	t20.WalkAway,
	t20.RingTime,
	t20.CallsAnswered,
	t1.Skillset_Default,
	t2.Not_Ready_Default,
	t3.Not_Rdy_Pull_Default,
	t4.Breaks,
	t5.[Comfort Break],
	t6.Training,
	t7.Meetings,
	t8.Reflection,
	t9.Other,
	t10.[System Failure],
	t11.[Wrap Time],
	t12.Research,
	t13.[Postal Work],
	t14.[Follow Up],
	t15.[Child Protection],
	t16.[Vulnerable Adults],
	t17.[Rule of Thumb],
	t18.[TAL Activity],
	t19.[Nurse Assessment]
order by
	t20.Skill,
	t20.Date,
	t20.Time

I think it's all the left joins at the end but I can't think of a way round it.
Thanks in advance.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-10-19 : 06:42:03

I've just created a unique identifier by concatenating Skill, Date and Time into one field for each of the select queries which build a temp table.
The last section of code now reads :-


----------------------  Amalgamate into one table ----------------------------------------
select
	t20.*,
	isnull(t1.Skillset_Default,0) as [Skillset Default],
	isnull(t2.Not_Ready_Default,0) as Not_Ready_Default,
	isnull(t3.Not_Rdy_Pull_Default,0) as Not_Rdy_Pull_Default,
	isnull(t4.Breaks,0) as Breaks,
	isnull(t5.[Comfort Break],0) as[Comfort Break],
	isnull(t6.Training,0) as Training,
	isnull(t7.Meetings,0) as Meetings,
	isnull(t8.Reflection,0) as Reflection,
	isnull(t9.Other,0) as Other,
	isnull(t10.[System Failure],0) as [System Failure],
	isnull(t11.[Wrap Time],0) as [Wrap Time],
	isnull(t12.Research,0) as Research,
	isnull(t13.[Postal Work],0) as [Postal Work],
	isnull(t14.[Follow Up],0) as [Follow Up],
	isnull(t15.[Child Protection],0) as [Child Protection],
	isnull(t16.[Vulnerable Adults],0) as [Vulnerable Adults],
	isnull(t17.[Rule of Thumb],0) as [Rule of Thumb],
	isnull(t18.[TAL Activity],0) as [TAL Activity],
	isnull(t19.[Nurse Assessment],0) as [Nurse Assessment]
from
	#temptable20 t20
		left join #temptable1 t1 on
			t1.Unique_ID=t20.Unique_ID
		left join #temptable2 t2 on 
			t2.Unique_ID=t20.Unique_ID
		left join #temptable3 t3 on 
			t3.Unique_ID=t20.Unique_ID
		left join #temptable4 t4 on 
			t4.Unique_ID=t20.Unique_ID
		left join #temptable5 t5 on 
			t5.Unique_ID=t20.Unique_ID
		left join #temptable6 t6 on 
			t6.Unique_ID=t20.Unique_ID
		left join #temptable7 t7 on 
			t7.Unique_ID=t20.Unique_ID
		left join #temptable8 t8 on 
			t8.Unique_ID=t20.Unique_ID
		left join #temptable9 t9 on 
			t9.Unique_ID=t20.Unique_ID
		left join #temptable10 t10 on 
			t10.Unique_ID=t20.Unique_ID
		left join #temptable11 t11 on 
			t11.Unique_ID=t20.Unique_ID
		left join #temptable12 t12 on 
			t12.Unique_ID=t20.Unique_ID
		left join #temptable13 t13 on 
			t13.Unique_ID=t20.Unique_ID
		left join #temptable14 t14 on 
			t14.Unique_ID=t20.Unique_ID
		left join #temptable15 t15 on 
			t15.Unique_ID=t20.Unique_ID
		left join #temptable16 t16 on 
			t16.Unique_ID=t20.Unique_ID
		left join #temptable17 t17 on 
			t17.Unique_ID=t20.Unique_ID
		left join #temptable18 t18 on 
			t18.Unique_ID=t20.Unique_ID
		left join #temptable19 t19 on 
			t19.Unique_ID=t20.Unique_ID
order by
	t20.Skill,
	t20.Date,
	t20.Time

Still seems to be working very slowly though.
Is it just the number of joins I have at the end?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-19 : 06:59:17

One LEFT JOIN is all you need to fight this EAV mess!

USE SymposiumDW
GO

DECLARE	@Start_Date DATE,
	@End_Date DATE

SELECT	@Start_Date = '20101004',	-- Inclusive
	@End_Date = '20101011'		-- Not inclusive

;WITH cteData
AS (
	SELECT		al.SkillsetName AS Skill,
			CAST(cs.[Timestamp] AS DATE) AS [Date],
			CAST(cs.[Timestamp] AS TIME(0)) AS [Time],
			MAX(CASE WHEN cs.ActivityCode = '00' THEN cs.ActivityTime ELSE 0 END) AS Skillset_Default,
			MAX(CASE WHEN cs.ActivityCode = '000' THEN cs.ActivityTime ELSE 0 END) AS Not_Ready_Default,
			MAX(CASE WHEN cs.ActivityCode = '0000' THEN cs.ActivityTime ELSE 0 END) AS Not_Rdy_Pull_Default,
			MAX(CASE WHEN cs.ActivityCode = '01' THEN cs.ActivityTime ELSE 0 END) AS Breaks,
			MAX(CASE WHEN cs.ActivityCode = '02' THEN cs.ActivityTime ELSE 0 END) AS [Comfort Break],
			MAX(CASE WHEN cs.ActivityCode = '03' THEN cs.ActivityTime ELSE 0 END) AS Training,
			MAX(CASE WHEN cs.ActivityCode = '04' THEN cs.ActivityTime ELSE 0 END) AS Meetings,
			MAX(CASE WHEN cs.ActivityCode = '05' THEN cs.ActivityTime ELSE 0 END) AS Reflection,
			MAX(CASE WHEN cs.ActivityCode = '06' THEN cs.ActivityTime ELSE 0 END) AS Other,
			MAX(CASE WHEN cs.ActivityCode = '07' THEN cs.ActivityTime ELSE 0 END) AS [System Failure],
			MAX(CASE WHEN cs.ActivityCode = '08' THEN cs.ActivityTime ELSE 0 END) AS [Wrap Time],
			MAX(CASE WHEN cs.ActivityCode = '09' THEN cs.ActivityTime ELSE 0 END) AS Research,
			MAX(CASE WHEN cs.ActivityCode = '10' THEN cs.ActivityTime ELSE 0 END) AS [Postal Work],
			MAX(CASE WHEN cs.ActivityCode = '11' THEN cs.ActivityTime ELSE 0 END) AS [Follow Up],
			MAX(CASE WHEN cs.ActivityCode = '12' THEN cs.ActivityTime ELSE 0 END) AS [Child Protection],
			MAX(CASE WHEN cs.ActivityCode = '13' THEN cs.ActivityTime ELSE 0 END) AS [Vulnerable Adults],
			MAX(CASE WHEN cs.ActivityCode = '14' THEN cs.ActivityTime ELSE 0 END) AS [Rule of Thumb],
			MAX(CASE WHEN cs.ActivityCode = '15' THEN cs.ActivityTime ELSE 0 END) AS [TAL Activity],
			MAX(CASE WHEN cs.ActivityCode = '22' THEN cs.ActivityTime ELSE 0 END) AS [Nurse Assessment]
	FROM		dbo.FACT_iActivityCodeStat AS cs
	INNER JOIN	dbo.V_IRTAgentList AS al on al.UserID = cs.UserID
				AND al.SkillsetName IN ('Health Advisor', 'Nurse Advisor', 'Dental Nurse', 'HI Advisor')
	WHERE		cs.[Timestamp] >= @Start_Date
			AND cs.[Timestamp] < @End_Date
			AND cs.ActivityCode IN ('00', '000', '0000', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '22')
	GROUP BY	al.SkillsetName,
			CAST(cs.[Timestamp] AS DATE),
			CAST(cs.[Timestamp] AS TIME(0))
), cteSource
AS (
	SELECT		al.SkillsetName AS Skill,
			CAST(aps.[Timestamp] AS DATE) AS [Date],
			CAST(aps.[Timestamp] AS TIME(0)) AS [Time],
			aps.LoggedInTime AS [Logged In],
			aps.TalkTime AS [Talk Time],
			aps.HoldTime AS [Hold Time],
			aps.WaitingTime AS [Wait Time],
			aps.WalkawayTime AS WalkAway,
			aps.RingTime AS RingTime,
			aps.CallsAnswered
	FROM		dbo.V_IRTAgentList AS al
	INNER JOIN	dbo.FACT_iAgentPerformanceStat AS aps on aps.UserID = al.UserID
				AND aps.[Timestamp] >= @Start_Date
				AND aps.[Timestamp] < @End_Date
	WHERE		al.SkillsetName IN ('Health Advisor', 'Nurse Advisor', 'Dental Nurse', 'HI Advisor')
)
SELECT		s.Skill,
		s.[Date],
		s.[Time],
		s.[Logged In],
		s.[Talk Time],
		s.[Hold Time],
		s.[Wait Time],
		s.WalkAway,
		s.RingTime,
		s.CallsAnswered
		1.E * (s.[Talk Time] + s.[Hold Time] + s.[Wait Time] + COALESCE(d.[Wrap Time], 0) + s.WalkAway) / s.CallsAnswered AS AHT,
		COALESCE(d.Skillset_Default, 0) AS [Skillset Default],
		COALESCE(d.Not_Ready_Default, 0) AS Not_Ready_Default,
		COALESCE(d.Not_Rdy_Pull_Default, 0) AS Not_Rdy_Pull_Default,
		COALESCE(d.Breaks, 0) AS Breaks,
		COALESCE(d.[Comfort Break], 0) AS [Comfort Break],
		COALESCE(d.Training, 0) AS Training,
		COALESCE(d.Meetings, 0) AS Meetings,
		COALESCE(d.Reflection, 0) AS Reflection,
		COALESCE(d.Other, 0) AS Other,
		COALESCE(d.[System Failure], 0) AS [System Failure],
		COALESCE(d.[Wrap Time], 0) AS [Wrap Time],
		COALESCE(d.Research, 0) AS Research,
		COALESCE(d.[Postal Work] ,0) AS [Postal Work],
		COALESCE(d.[Follow Up], 0) AS [Follow Up],
		COALESCE(d.[Child Protection], 0) AS [Child Protection],
		COALESCE(d.[Vulnerable Adults], 0) AS [Vulnerable Adults],
		COALESCE(d.[Rule of Thumb], 0) AS [Rule of Thumb],
		COALESCE(d.[TAL Activity], 0) AS [TAL Activity],
		COALESCE(d.[Nurse Assessment], 0) AS [Nurse Assessment]
FROM		cteSource AS s
LEFT JOIN	cteDate AS d ON d.Skill = s.Skill
			AND d.[Date] = s.[Date]
			and s.[Time] = s.[Time]
ORDER BY	s.Skill,
		s.[Date],
		s.[Time]

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-19 : 07:10:01

For more performance, create these two indexes

CREATE NONCLUSTERED INDEX IX_Peso ON dbo.FACT_iActivityCodeStat ([Timestamp], ActivityCode) INCLUDE (UserID, ActivityTime)
CREATE NONCLUSTERED INDEX IX_Peso ON dbo.FACT_iAgentPerformanceStat ([Timestamp]) INCLUDE (UserID, LoggedInTime, TalkTime, HoldTime, WaitingTime, WalkawayTime, RingTime, CallsAnswered)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources