Stored procedure hanging - 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

Stored procedure hanging

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-12-05 : 09:07:44

Hi all

I've got a stored procedure (that I inhertied) that seems to be taking far longer than it should to run and is hogging 50% of the available CPU power.

When it's running, the CPU goes to 50% and stays there (this is a server running 2 x Xeon processors). I can get the rest of the specs if anyone needs them.

This is the SPROC (scripted as a CREATE statement):-


USE [Reports]
GO

/****** Object:  StoredProcedure [dbo].[Populate_rpt_DH111MinimumDataSet_Tel_with_NW111]    Script Date: 12/05/2012 14:01:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[Populate_rpt_DH111MinimumDataSet_Tel_with_NW111]

		@Start	datetime,
		@End	datetime
AS

---------------------------------------------------------------------------------------------
-- INFORMATION HEADER - INCLUDES: CHANGE CONTROL
---------------------------------------------------------------------------------------------

-- Assumes the Telephony data from FCMS has been loaded into the Staging

-- Process

-- Copy FCMS Call By Call to Temporary Table
-- Delete matching records in rpt_DH111_NW_FCMS_Tel_CallByCall in Reports with Temporary table
-- Copy FCMS Call By Call to rpt_DH111_NW_FCMS_Tel_CallByCall in Reports
-- Generate a 15 minute roll up version as rpt_DH111_NW_FCMS_Tel_Intervals in Reports
-- Mash the data together...

-- SQL Populates: 
--
-- By pulling FCMS data in from Staging.dbo.FCMS_Tel_Loader
-- into #FCMS_Tel_CallByCall
-- copy and update into Reports.dbo.rpt_DH111_NW_FCMS_Tel_CallByCall
-- then into #FCMS_Tel_Calls_To_ByIntervals
-- copy and update into Reports.dbo.rpt_DH111_NW_FCMS_Tel_ByIntervals


-- By pulling FCMS data in from Staging.dbo.NWAS_Tel_Loader
--

-- Via

-- Data ends up in Reports.dbo.rpt_DH111MinimumDataSet_Tel
-- 
--
-- TO DO:
-- 1. DONE - Nurse Warm Transfer Times
-- 2. DONE - Review the [mds_Name] usage
-- 3. DONE - Remove the calls that were overflowed to NHS Direct to avoid double counting (v0.4).
-- 4. DONE - Fix bug that sets Warm transfer times between FCMS and NHSD as the same
--		   - added truncate table for truncate table #tmp_WarmTransfer (v0.51)
-- 5. DONE - Fix Data Quality Error - NHSD Overflowed Offered calls not being counted in output. (v0.52)
-- 6. DONE - Fix Data Quality Error - FCMS Overflowed double counted Offered in output. (v0.53)
-- 7. Integrate NWAS Telephony (0.60)
-- 8. Minor changes (0.61)

---------------------------------------------------------------------------------------------
-- DECLARATIONS OF TEMPORARY VARIABLES AND TABLES
---------------------------------------------------------------------------------------------

-- Troubleshooting

--declare @Start datetime
--set @Start = '01/04/2012 00:00:00'			-- when the service soft launch was...
--declare @End datetime
--set @End = '10/04/2012 23:59:59'

-- Clear out temporary tables

set nocount on
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Starting Telephony load'
	,GETDATE()

IF  EXISTS (SELECT * FROM tempdb.sys.tables WHERE name like '#FCMS_Tel_ByIntervals___%')
drop table #FCMS_Tel_ByIntervals
IF  EXISTS (SELECT * FROM tempdb.sys.tables WHERE name like '#NHSD_Tel_ByIntervals___%')
drop table #NHSD_Tel_ByIntervals
IF  EXISTS (SELECT * FROM tempdb.sys.tables WHERE name like '#Combined_Tel_ByIntervals___%')
drop table #Combined_Tel_ByIntervals
IF  EXISTS (SELECT * FROM tempdb.sys.tables WHERE name like '#ShortCallAnswered___%')
drop table #ShortCallAnswered
IF  EXISTS (SELECT * FROM tempdb.sys.tables WHERE name like '#ApplicationIDs___%')
drop table #ApplicationIDs
IF  EXISTS (SELECT * FROM tempdb.sys.tables WHERE name like '#tmp_WarmTransfer___%')
drop table #tmp_WarmTransfer
IF  EXISTS (SELECT * FROM tempdb.sys.tables WHERE name like '#NWAS_Tel_ByIntervals___%')
drop table #NWAS_Tel_ByIntervals

-- Define working tables
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Creating temp tables'
	,GETDATE()

CREATE TABLE #FCMS_Tel_CallByCall				-- Working Table that holds the Call By Call data from FCMS
(
	[mds_Call_Start_Date_Time]				[datetime] NULL,
	[mds_Call_End_Date_Time]				[datetime] NULL,
	[mds_Call_Connect_Time_Seconds]			[int] NULL,
	[mds_Call_rowguid]						[varchar](50) NOT NULL,
	[mds_Name]								[varchar](50) NULL,
	[mds_Callers_Number]					[varchar](50) NULL,
	[mds_Number_Dialled]					[int] NULL,
	[mds_Calls_Offered]						[int] NULL,
	[mds_Calls_Abandoned]					[int] NULL,
	[mds_Calls_Answered]					[int] NULL,
	[mds_Calls_Abd_Over30]					[int] NULL,
	[mds_Calls_Abd_Over60]					[int] NULL,
	[mds_Calls_Ans_Over60]					[int] NULL,
	[mds_Short_Calls_Answered]				[int] NULL,
	[mds_Nurse_Warm_Transfers_Answered]		[int] NULL,
	[mds_Nurse_Warm_Transfers_Answer_Delay]	[int] NULL,
	[mds_Overflow_Ans_within_15]			[int] NULL,
	[mds_Ring_Time]							[int] NULL,
	[mds_Talk_Time]							[int] NULL,
	[mds_Overflow_Out]						[int] NULL,
)

CREATE TABLE #FCMS_Tel_Calls_To_ByIntervals			-- Working Table that holds the rolled up view of data from FCMS
(
	[mds_Day]								[date] NULL,
	[mds_Time]								[time] (7) NULL,
	[mds_Call_Connect_Time_Seconds]			[int] NULL,
	[mds_Name]								[varchar](50) NULL,
	[mds_Calls_Offered]						[int] NULL,
	[mds_Calls_Abandoned]					[int] NULL,
	[mds_Calls_Answered]					[int] NULL,
	[mds_Calls_Abd_Over30]					[int] NULL,
	[mds_Calls_Abd_Over60]					[int] NULL,
	[mds_Calls_Ans_Over60]					[int] NULL,
	[mds_Short_Calls_Answered]				[int] NULL,
	[mds_Nurse_Warm_Transfers_Answered]		[int] NULL,
	[mds_Nurse_Warm_Transfers_Answer_Delay]	[int] NULL,
	[mds_Overflow_Ans_within_15]			[int] NULL,
	[mds_Ring_Time]							[int] NULL,
	[mds_Talk_Time]							[int] NULL,
	[ext_Timestamp]							[datetime] NULL,
	[ext_Service]							[varchar] (60) NULL,
	[ext_SubService]						[varchar] (60) NULL,
	[ext_Hour]								[int] NULL,
	[ext_RollingWeekNo]						[int] NULL,
	[ext_YearMonth]							[varchar] (7) NULL	
)

CREATE TABLE #FCMS_Tel_ByIntervals			-- Working Table that holds the Values to calculate the FCMS telephony stats
(
	[mds_Day]								[date] NOT NULL,
	[mds_Time]								[time](7) NOT NULL,
	[mds_CallsOffered]						[int] NOT NULL,
	[mds_CallsAbandoned]					[int] NOT NULL,
	[mds_CallsAnswered]						[int] NOT NULL,
	[mds_AbdOver30]							[int] NOT NULL,
	[mds_AnsOver60]							[int] NOT NULL,
	[mds_Name]								[varchar](60) NOT NULL,
	[mds_ShortCallsAnswered]				[int] NOT NULL,
	[mds_NurseWarmTransfersAnswered]		[int] NOT NULL,
	[mds_NurseWarmTransfersAnswerDelay]		[int] NOT NULL,
	[ext_Timestamp]							[datetime] NOT NULL,
	[ext_Service]							[varchar](60) NOT NULL,
	[ext_SubService]						[varchar](60) NOT NULL,
	[ext_Hour]								[int] NULL,
	[ext_RollingWeekNo]						[int] NULL,
	[ext_YearMonth]							[varchar](7) NULL
)

-- Added in v0.6

CREATE TABLE #NWAS_Tel_ByIntervals_FromStaging				-- was #StagingCopy_NWAS_Tel_ByIntervals in v0.6
(
	[mds_Call_Start_Date_Time]				[datetime] NULL,
	[mds_Call_End_Date_Time]				[datetime] NULL,
	[mds_Call_Connect_Time_seconds]			[int] NULL,
	[mds_Call_rowguid]						[varchar](50) NOT NULL,
	[mds_Name]								[varchar](50) NULL,
	[mds_Callers_Number]					[varchar](50) NULL,
	[mds_Number_Dialled]					[int] NULL,
	[mds_Calls_Offered]						[int] NULL,
	[mds_Calls_Abandoned]					[int] NULL,
	[mds_Calls_Answered]					[int] NULL,
	[mds_Calls_Abd_over30]					[int] NULL,
	[mds_Calls_Abd_over60]					[int] NULL,
	[mds_Calls_Ans_Over60]					[int] NULL,
	[mds_Short_Calls_Answered]				[int] NULL,
	[mds_Nurse_Warm_Transfers_Answered]		[int] NULL,
	[mds_Nurse_Warm_Transfers_Answer_Delay]	[int] NULL,
	[mds_Overflow_ans_within_15]			[int] NULL,
	[mds_ring_time]							[int] NULL,
	[mds_talk_time]							[int] NULL,
	[mds_overflow_out]						[int] NULL
)

-- Added in v0.6

CREATE TABLE #NWAS_Tel_ByIntervals			-- Working Table that holds the Values to calculate the NWAS telephony stats
(
	[mds_Day]								[date] NOT NULL,
	[mds_Time]								[time](7) NOT NULL,
	[mds_CallsOffered]						[int] NOT NULL,
	[mds_CallsAbandoned]					[int] NOT NULL,
	[mds_CallsAnswered]						[int] NOT NULL,
	[mds_AbdOver30]							[int] NOT NULL,
	[mds_AnsOver60]							[int] NOT NULL,
	[mds_Name]								[varchar](60) NOT NULL,
	[mds_ShortCallsAnswered]				[int] NOT NULL,
	[mds_NurseWarmTransfersAnswered]		[int] NOT NULL,
	[mds_NurseWarmTransfersAnswerDelay]		[int] NOT NULL,
	[ext_Timestamp]							[datetime] NOT NULL,
	[ext_Service]							[varchar](60) NOT NULL,
	[ext_SubService]						[varchar](60) NOT NULL,
	[ext_Hour]								[int] NULL,
	[ext_RollingWeekNo]						[int] NULL,
	[ext_YearMonth]							[varchar](7) NULL
)

CREATE TABLE #NHSD_Tel_ByIntervals			-- Working Table that holds the Values to calculate the NHSD telephony stats
(
	[mds_Day]								[date] NOT NULL,
	[mds_Time]								[time](7) NOT NULL,
	[mds_CallsOffered]						[int] NOT NULL,
	[mds_CallsAbandoned]					[int] NOT NULL,
	[mds_CallsAnswered]						[int] NOT NULL,
	[mds_AbdOver30]							[int] NOT NULL,
	[mds_AnsOver60]							[int] NOT NULL,
	[mds_Name]								[varchar](60) NOT NULL,
	[mds_ShortCallsAnswered]				[int] NOT NULL,
	[mds_NurseWarmTransfersAnswered]		[int] NOT NULL,
	[mds_NurseWarmTransfersAnswerDelay]		[int] NOT NULL,
	[ext_Timestamp]							[datetime] NOT NULL,
	[ext_Service]							[varchar](60) NOT NULL,
	[ext_SubService]						[varchar](60) NOT NULL,
	[ext_Hour]								[int] NULL,
	[ext_RollingWeekNo]						[int] NULL,
	[ext_YearMonth]							[varchar](7) NULL
)

CREATE TABLE #Combined_Tel_ByIntervals			-- Working Table that holds the Values to calculate the Combined NHSD and FCMS telephony stats
(
	[mds_Day]								[date] NOT NULL,
	[mds_Time]								[time](7) NOT NULL,
	[mds_CallsOffered]						[int] NOT NULL,
	[mds_CallsAbandoned]					[int] NOT NULL,
	[mds_CallsAnswered]						[int] NOT NULL,
	[mds_AbdOver30]							[int] NOT NULL,
	[mds_AnsOver60]							[int] NOT NULL,
	[mds_Name]								[varchar](60) NOT NULL,
	[mds_ShortCallsAnswered]				[int] NOT NULL,
	[mds_NurseWarmTransfersAnswered]		[int] NOT NULL,
	[mds_NurseWarmTransfersAnswerDelay]		[int] NOT NULL,
	[ext_Timestamp]							[datetime] NOT NULL,
	[ext_Service]							[varchar](60) NOT NULL,
	[ext_SubService]						[varchar](60) NOT NULL,
	[ext_Hour]								[int] NULL,
	[ext_RollingWeekNo]						[int] NULL,
	[ext_YearMonth]							[varchar](7) NULL
)

CREATE TABLE #ShortCallAnswered				-- Working Table that holds the Values to calculate the Short Call stats
(
	[Timestamp]								[datetime] NULL,
	[SubService]							[varchar] (60) NULL,
	[ShortCallsAnswered]					[int] NULL
)

CREATE TABLE #ApplicationIDs
(
	[ApplicationID]							[int] NULL,		
	[SubService]							[varchar] (60) NULL
)

CREATE TABLE #tmp_WarmTransfer
(
	 [Timestamp]							[datetime] NULL,
	 [NurseWarmTransfersAnswered]			[int] NULL,
	 [NurseWarmTransfersAnswerDelay]		[int] NULL
)

---------------------------------------------------------------------------------------------
-- 1. MOVE NEW DATA TO ARCHIVE AND CREATE INTERVAL VERSION
---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------
-- 1.A. FCMS DATA
---------------------------------------------------------------------------------------------

-- Generate a 15 minute roll up version as rpt_DH111_NW_FCMS_Tel_Intervals in Reports
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Starting FCMS data to 15-minute level'
	,GETDATE()

insert into #FCMS_Tel_Calls_To_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_Call_Connect_Time_Seconds],
	[mds_Name],
	[mds_Calls_Offered],
	[mds_Calls_Abandoned],
	[mds_Calls_Answered],
	[mds_Calls_Abd_Over30],
	[mds_Calls_Abd_Over60],
	[mds_Calls_Ans_Over60],
	[mds_Short_Calls_Answered],
	[mds_Nurse_Warm_Transfers_Answered],
	[mds_Nurse_Warm_Transfers_Answer_Delay],
	[mds_Overflow_Ans_within_15],
	[mds_Ring_Time],
	[mds_Talk_Time],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select distinct 
	cast( mds_Call_Start_Date_Time as date )			as [mds_Day],
	cast( cal.dtmDateTime as time )						as [mds_Time],
	sum( [mds_Call_Connect_Time_Seconds] )				as [mds_Call_Connect_Time_Seconds],
	[mds_Name]											as [mds_Name],
	sum( [mds_Calls_Offered] )							as [mds_Calls_Offered],
	sum( [mds_Calls_Abandoned] )						as [mds_Calls_Abandoned],						-- case statement removed in v0.53
	sum( [mds_Calls_Answered] )							as [mds_Calls_Answered],						-- case statement removed in v0.53
	sum( [mds_Calls_Abd_Over30] )						as [mds_Calls_Abd_Over30],						-- case statement removed in v0.53
	sum( [mds_Calls_Abd_Over60] )						as [mds_Calls_Abd_Over60],						-- case statement removed in v0.53
	sum( [mds_Calls_Ans_Over60] )						as [mds_Calls_Ans_Over60],						-- case statement removed in v0.53
	sum( [mds_Short_Calls_Answered] )					as [mds_Short_Calls_Answered],					-- case statement removed in v0.53
	0													as [mds_Nurse_Warm_Transfers_Answered],			-- Not a FCMS measure
	0													as [mds_Nurse_Warm_Transfers_Answer_Delay],		-- Not a FCMS measure
	sum( [mds_Overflow_Ans_within_15] )					as [mds_Overflow_Ans_within_15],				-- case statement removed in v0.53
	sum( [mds_Ring_Time] )								as [mds_Ring_Time],
	sum( [mds_Talk_Time] )								as [mds_Talk_Time],
	cal.dtmDateTime										as [ext_Timestamp],
	'North West'										as [ext_Service],
	case
		when [mds_Name] = '111 Inbound Calls'
			then 'North West 111'
		when [mds_Name] = '111 Overflow Calls'
			then 'North West 111'
		else 'UNMATCHED - Populate_rpt_DH111MinimumDataSet_Tel_with_NW111'
	end													as [ext_SubService],
	intHour												as [ext_Hour],
	null												as [ext_RollingWeekNo],
	null												as [ext_YearMonth]
from	Reports.dbo.rpt_DH111_NW_FCMS_Tel_CallByCall (nolock)	rpt,
		Reference.dbo.ref_Calendar15MinuteInterval (nolock)		cal
where rpt.mds_Call_Start_Date_Time between @start and @end
and cal.dtmDateTime between @start and @end
and mds_Call_Start_Date_Time between cal.dtmDateTime and DATEADD( mi, 15, cal.dtmDateTime )
and mds_Overflow_Out = 0																			 --	Added in v0.53 to avoid double counting calls.
group by cast( mds_Call_Start_Date_Time as date ), cal.dtmDateTime, intHour, [mds_Name]
order by [mds_Time]

-- update [ext_RollingWeekNo] and [ext_YearMonth]

update #FCMS_Tel_Calls_To_ByIntervals
set #FCMS_Tel_Calls_To_ByIntervals.ext_RollingWeekNo = cal.intRollingWeekNo,
	#FCMS_Tel_Calls_To_ByIntervals.ext_YearMonth = cal.strYearMonth
from #FCMS_Tel_Calls_To_ByIntervals			tmp
	inner join Reference.dbo.ref_Calendar	cal
		on tmp.[mds_Day] = cal.[dtDate]

-- delete matching records in rpt_DH111_NW_FCMS_Tel_ByIntervals
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Load FCMS 15-minte level data into FCMS table'
	,GETDATE()

delete
from Reports.dbo.rpt_DH111_NW_FCMS_Tel_ByIntervals
where ext_Timestamp in ( select distinct ext_Timestamp from #FCMS_Tel_Calls_To_ByIntervals )

-- Insert records from #FCMS_Tel_ByIntervals into rpt_DH111_NW_FCMS_Tel_ByIntervals
-- mds_Overflow_Out removed below in v0.53

insert into Reports.dbo.rpt_DH111_NW_FCMS_Tel_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_Call_Connect_Time_Seconds],
	[mds_Name],
	[mds_Calls_Offered],
	[mds_Calls_Abandoned],
	[mds_Calls_Answered],
	[mds_Calls_Abd_Over30],
	[mds_Calls_Abd_Over60],
	[mds_Calls_Ans_Over60],
	[mds_Short_Calls_Answered],
	[mds_Nurse_Warm_Transfers_Answered],
	[mds_Nurse_Warm_Transfers_Answer_Delay],
	[mds_Overflow_Ans_within_15],
	[mds_Ring_Time],
	[mds_Talk_Time],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select distinct 
	[mds_Day],
	[mds_Time],
	[mds_Call_Connect_Time_Seconds],
	[mds_Name],
	[mds_Calls_Offered],
	[mds_Calls_Abandoned],
	[mds_Calls_Answered],
	[mds_Calls_Abd_Over30],
	[mds_Calls_Abd_Over60],
	[mds_Calls_Ans_Over60],
	[mds_Short_Calls_Answered],
	[mds_Nurse_Warm_Transfers_Answered],
	[mds_Nurse_Warm_Transfers_Answer_Delay],
	[mds_Overflow_Ans_within_15],
	[mds_Ring_Time],
	[mds_Talk_Time],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
from #FCMS_Tel_Calls_To_ByIntervals

-- drop the working tables
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Drop FCMS temp tables'
	,GETDATE()

drop table #FCMS_Tel_CallByCall
drop table #FCMS_Tel_Calls_To_ByIntervals

---------------------------------------------------------------------------------------------
-- 1.B NWAS DATA
---------------------------------------------------------------------------------------------

drop table #NWAS_Tel_ByIntervals_FromStaging

---------------------------------------------------------------------------------------------
-- 2. MERGE DATA INTO ONE VIEW TO COPY TO 
---------------------------------------------------------------------------------------------

-- Build the merger based on Archive ( Reports.rpt_DH111_NW_FCMS_Tel_ByIntervals and SymposiumDW tables )

----------------------------------------------------------------------------------------------------
-- 2.A. FCMS Performance
----------------------------------------------------------------------------------------------------

-- FCMS Step 1 - insert the FCMS elements with '111 Inbound'
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Starting FCMS telephony load'
	,GETDATE()

truncate table #FCMS_Tel_ByIntervals

insert into #FCMS_Tel_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select
	[mds_Day],
	[mds_Time],
	[mds_Calls_Offered]				as [mds_CallsOffered],
	[mds_Calls_Abandoned]			as [mds_CallsAbandoned],
	[mds_Calls_Answered]			as [mds_CallsAnswered],
	[mds_Calls_Abd_Over30]			as [mds_AbdOver30],
	[mds_Calls_Ans_Over60]			as [mds_AnsOver60],
	'FCMS performance FCMS data'	as [mds_Name],
	[mds_Short_Calls_Answered]		as [mds_ShortCallsAnswered],
	0								as [mds_NurseWarmTransfersAnswered],		-- set as "0" rather than "null" to avoid warnings.
	0								as [mds_NurseWarmTransfersAnswerDelay],		-- set as "0" rather than "null" to avoid warnings.
	[ext_Timestamp]					as [ext_Timestamp],
	[ext_Service]					as [ext_Service],
	[ext_SubService]				as [ext_SubService],
	0								as [ext_Hour],
	null							as [ext_RollingWeekNo],
	null							as [ext_YearMonth]
from Reports.dbo.rpt_DH111_NW_FCMS_Tel_ByIntervals (nolock)
where [ext_Timestamp] between @start and @end
and [mds_Name] = '111 Inbound Calls'
	
union

-- FCMS Step 2 - Now append the NHSD elements for FCMS

select
	cast( iA.[Timestamp] as date )					as [mds_Day],
	cast( iA.[Timestamp] as time )					as [mds_Time],
	iA.[CallsOffered]								as [mds_CallsOffered],						-- Added in v0.52
	iA.[CallsAbandoned]								as [mds_CallsAbandoned],
	iA.[CallsAnswered]								as [mds_CallsAnswered],
	iA.[CallsAbandoned]								as [mds_AbdOver30],							-- set as pure abandoned as if hit our system they have adb after 30 by default.
	iA.[CallsAnswered] - iA.[Ansin15_Overflow]		as [mds_AnsOver60],
	'FCMS performance NHSD data'					as [mds_Name],
	0												as [mds_ShortCallsAnswered],				-- Updated below.
	0												as [mds_NurseWarmTransfersAnswered],		-- set as "0" rather than "null" to avoid warnings.
	0												as [mds_NurseWarmTransfersAnswerDelay],		-- set as "0" rather than "null" to avoid warnings.
	iA.[Timestamp]									as [ext_Timestamp],
	'North West'									as [ext_Service],
	'North West 111'								as [ext_SubService],
	0												as [ext_Hour],
	null											as [ext_RollingWeekNo],
	null											as [ext_YearMonth]
from SymposiumDW.dbo.V_iApplicationStat_Derived (nolock)					iA
	inner join Reference.dbo.ref_ApplicationsToServices (nolock)			rfA
		on iA.Name = rfA.ApplicationName
	inner join Reference.dbo.vw_Calendar15MinuteIntervalEnhanced (nolock)	rfi
		on iA.[Timestamp] = rfi.[dtmDateTime]
where rfA.SubService = 'North West FCMS Overflow'
and iA.[Timestamp] between @start and @end

-- Merge NHSD and FCMS derived data together into #FCMS_Tel_ByIntervals
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Merge FCMS and NHSD data together'
	,GETDATE()

insert into #FCMS_Tel_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select distinct
	[mds_Day],
	[mds_Time],
	sum( [mds_CallsOffered] ),
	sum( [mds_CallsAbandoned] ),
	sum( [mds_CallsAnswered] ),
	sum( [mds_AbdOver30] ),
	sum( [mds_AnsOver60] ),
	[mds_Name],
	case
		when [mds_Name] = 'FCMS performance NHSD data'
		then 0
		else sum( [mds_ShortCallsAnswered] )
	end,
	0,
	0,
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	-99,						-- Flag for the merged component records to keep
	null,
	null
from #FCMS_Tel_ByIntervals
group by [mds_Day], [mds_Time], [mds_Name], [ext_Timestamp], [ext_Service], [ext_SubService]
order by [ext_Timestamp], [mds_Name]

-- Delete component records to leave final set row per source
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Get rid of un-needed records'
	,GETDATE()

delete
from #FCMS_Tel_ByIntervals
where [ext_Hour] <> -99

-- Update [mds_ShortCallsAnswered] -  for 'FCMS performance NHSD data' records only
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Collect any new telephony applications for FCMS overflow'
	,GETDATE()

truncate table #ApplicationIDs

insert into #ApplicationIDs				-- Do we need to include the hub?
(
	[ApplicationID],
	[SubService]
)
select
	dim.ApplicationID,
	ref.SubService
from SymposiumDW.dbo.DIM_Application (nolock)						dim
	inner join Reference.dbo.ref_ApplicationsToServices (nolock)	ref
		on ref.ApplicationName = dim.Name
where SubService = 'North West FCMS Overflow'

insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Start of FCMS short-calls'
	,GETDATE()

truncate table #ShortCallAnswered

insert into #ShortCallAnswered
(
	[Timestamp],
	[SubService],
	[ShortCallsAnswered]
)
SELECT DISTINCT
	a.[Timestamp]					as [Timestamp],
	tmp.SubService					as [SubService],
	sum(a.[ShortCallsAnswered])		as [ShortCallsAnswered]
FROM [SymposiumDW].[dbo].[FACT_iAgentPerformanceStat] (nolock)			a
	join [SymposiumDW].[dbo].[FACT_iAgentByApplicationStat] (nolock)	ap
		on a.HUBID = ap.hubid 
		and a.[Timestamp] = ap.[Timestamp]
		and a.AgentLogin = ap.agentlogin
	join #ApplicationIDs (nolock)										tmp 
		on tmp.ApplicationID = ap.ApplicationID
WHERE a.[Timestamp] BETWEEN @Start AND @End
group by a.[Timestamp], tmp.SubService

-- Update #FCMS_Tel_ByIntervals with Short Calls

update #FCMS_Tel_ByIntervals
set #FCMS_Tel_ByIntervals.[mds_ShortCallsAnswered] = #ShortCallAnswered.[ShortCallsAnswered]
from #FCMS_Tel_ByIntervals
	inner join #ShortCallAnswered
		on #FCMS_Tel_ByIntervals.ext_Timestamp = #ShortCallAnswered.[Timestamp]
		and #FCMS_Tel_ByIntervals.ext_SubService = #ShortCallAnswered.SubService
where [mds_Name] = 'FCMS performance NHSD data'

-- Update [mds_NurseWarmTransfersAnswered] and [mds_NurseWarmTransfersAnswerDelay] - Warm Transfered from FCMS
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Get warm transfer data'
	,GETDATE()

truncate table #tmp_WarmTransfer

insert into #tmp_WarmTransfer
(
	[Timestamp],
	[NurseWarmTransfersAnswered],
	[NurseWarmTransfersAnswerDelay]
)
select distinct
	 iAS.[Timestamp]										as [Timestamp],
	 sum( CallsAnswered )									as NurseWarmTransfersAnswered,
	 sum( CallsAnsweredDelay )								as NurseWarmTransfersAnswerDelay
from [SymposiumDW].[dbo].[FACT_iApplicationStat] (nolock)			iAS
	inner join [SymposiumDW].[dbo].[DIM_Application] (nolock)		dim
		on dim.HUBID = iAS.HUBID
		and dim.ApplicationID = iAS.ApplicationID
	inner join Reference.dbo.ref_ApplicationsToServices (nolock)	ref
		on ref.ApplicationName = dim.Name
where ServiceGroup = '111 Core'
and SubService = 'Warm Transfer Nurse (FCMS)'	-- Limits to FCMS Transfers to NHS Direct Only
and [Timestamp] between @Start AND @End
group by iAS.[Timestamp]
order by iAS.[Timestamp]

-- Update #FCMS_Tel_ByIntervals with Warm Transfers

update #FCMS_Tel_ByIntervals
set #FCMS_Tel_ByIntervals.mds_NurseWarmTransfersAnswered = #tmp_WarmTransfer.NurseWarmTransfersAnswered,
	#FCMS_Tel_ByIntervals.mds_NurseWarmTransfersAnswerDelay = #tmp_WarmTransfer.NurseWarmTransfersAnswerDelay
from #FCMS_Tel_ByIntervals
	inner join #tmp_WarmTransfer
		on #FCMS_Tel_ByIntervals.ext_Timestamp = #tmp_WarmTransfer.[Timestamp]
where #FCMS_Tel_ByIntervals.[mds_Name] = 'FCMS performance FCMS data'

-- Update [mds_NurseWarmTransfersAnswered] and [mds_NurseWarmTransfersAnswerDelay] - Warm Transfered within NHSD

-- Update #FCMS_Tel_ByIntervals with Warm Transfers
-- NOT UPDATED AS NOT RELEVANT FOR FCMS DATA - ADDED BELOW AT NHS DIRECT SIDE.  (SET TO ZERO)

update #FCMS_Tel_ByIntervals
set #FCMS_Tel_ByIntervals.mds_NurseWarmTransfersAnswered = 0,
	#FCMS_Tel_ByIntervals.mds_NurseWarmTransfersAnswerDelay = 0
from #FCMS_Tel_ByIntervals
	inner join #tmp_WarmTransfer
		on #FCMS_Tel_ByIntervals.ext_Timestamp = #tmp_WarmTransfer.[Timestamp]
where #FCMS_Tel_ByIntervals.[mds_Name] = 'FCMS performance NHSD data'

-- FINAL UPDATES FOR FCMS

-- Update [ext_Hour]

update #FCMS_Tel_ByIntervals
set #FCMS_Tel_ByIntervals.[ext_Hour] = cal.[intHour]
from #FCMS_Tel_ByIntervals											tmp
	 inner join Reference.dbo.ref_Calendar15MinuteInterval			cal
		on tmp.[ext_Timestamp] = cal.dtmDateTime
where tmp.[ext_Timestamp] between @start and @end

-- update [ext_RollingWeekNo] and [ext_YearMonth]

update #FCMS_Tel_ByIntervals
set #FCMS_Tel_ByIntervals.ext_RollingWeekNo = cal.intRollingWeekNo,
	#FCMS_Tel_ByIntervals.ext_YearMonth = cal.strYearMonth
from #FCMS_Tel_ByIntervals					tmp
	inner join Reference.dbo.ref_Calendar	cal
		on tmp.[mds_Day] = cal.[dtDate]

---------------------------------------------------------------------------------------------
-- 2.B. NWAS TO MERGE
---------------------------------------------------------------------------------------------

-- Update working table for telephony #NWAS_Tel_ByIntervals from NWAS Source
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Get initial NWAS data (already at 15-minute level)'
	,GETDATE()

truncate table #NWAS_Tel_ByIntervals

insert into #NWAS_Tel_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
SELECT
	cast( rpt.[mds_Call_Start_Date_Time] as date )	as [mds_Day],
	cast( rpt.[mds_Call_Start_Date_Time] as time )	as [mds_Time],
	rpt.[mds_Calls_Offered]							as [mds_CallsOffered],
    rpt.[mds_Calls_Abandoned]						as [mds_CallsAbandoned],
    rpt.[mds_Calls_Answered]						as [mds_CallsAnswered],
	rpt.[mds_Calls_Abd_over30]						as [mds_AbdOver30],
    rpt.[mds_Calls_Ans_Over60]						as [mds_AnsOver60],
	'NWAS performance NWAS data'					as [mds_Name],
	rpt.[mds_Short_Calls_Answered]					as [mds_ShortCallsAnswered],
	0												as [mds_NurseWarmTransfersAnswered],
	0												as [mds_NurseWarmTransfersAnswerDelay],
	rpt.[mds_Call_Start_Date_Time]					as [ext_Timestamp],
	'North West'									as [ext_Service],
	'North West 111'								as [ext_SubService],
	0												as [ext_Hour],
	null											as [ext_RollingWeekNo],
	null											as [ext_YearMonth]
FROM Reports.dbo.rpt_DH111_NW_NWAS_Tel_ByIntervals (nolock)		rpt
where rpt.mds_Call_Start_Date_Time between @start and @end

union 

-- Append the NHSD elements for NWAS

select distinct
	cast( iA.[Timestamp] as date )									as [mds_Day],
	cast( iA.[Timestamp] as time )									as [mds_Time],
	0																as [mds_CallsOffered],						-- Unlike FCMS this would double count.
	sum( iA.[CallsAbandoned] )										as [mds_CallsAbandoned],
	sum( iA.[CallsAnswered] )										as [mds_CallsAnswered],
	sum( iA.[CallsAbandoned] )										as [mds_AbdOver30],							-- set as pure abandoned as if hit our system they have adb after 30 by default.
	sum( iA.[CallsAnswered] ) - sum( iA.[Ansin15_Overflow] )		as [mds_AnsOver60],
	'NWAS performance NHSD data'									as [mds_Name],
	0																as [mds_ShortCallsAnswered],				-- Updated below.
	0																as [mds_NurseWarmTransfersAnswered],		-- set as "0" rather than "null" to avoid warnings.
	0																as [mds_NurseWarmTransfersAnswerDelay],		-- set as "0" rather than "null" to avoid warnings.
	iA.[Timestamp]													as [ext_Timestamp],
	'North West'													as [ext_Service],
	'North West 111'												as [ext_SubService],
	0																as [ext_Hour],
	null															as [ext_RollingWeekNo],
	null															as [ext_YearMonth]
from SymposiumDW.dbo.V_iApplicationStat_Derived (nolock)					iA
	inner join Reference.dbo.ref_ApplicationsToServices (nolock)			rfA
		on iA.Name = rfA.ApplicationName
where rfA.SubService = 'North West NWAS Overflow'
and iA.[Timestamp] between @start and @end
group by cast( iA.[Timestamp] as date ), cast( iA.[Timestamp] as time ), iA.[Timestamp]


-- Merge NHSD and NWAS derived data together into #NWAS_Tel_ByIntervals
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Merge NWAS and NHSD data together'
	,GETDATE()

insert into #NWAS_Tel_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select distinct
	[mds_Day],
	[mds_Time],
	sum( [mds_CallsOffered] ),
	sum( [mds_CallsAbandoned] ),
	sum( [mds_CallsAnswered] ),
	sum( [mds_AbdOver30] ),
	sum( [mds_AnsOver60] ),
	[mds_Name],
	case
		when [mds_Name] = 'NWAS performance NHSD data'
		then 0
		else sum( [mds_ShortCallsAnswered] )
	end,
	0,
	0,
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	-99,						-- Flag for the merged component records to keep
	null,
	null
from #NWAS_Tel_ByIntervals
group by [mds_Day], [mds_Time], [mds_Name], [ext_Timestamp], [ext_Service], [ext_SubService]
order by [ext_Timestamp], [mds_Name]

-- Delete component records to leave final set row per source
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Get rid of un-needed records'
	,GETDATE()

delete
from #NWAS_Tel_ByIntervals
where [ext_Hour] <> -99

-- Update [mds_ShortCallsAnswered] -  for 'NWAS performance NHSD data' records only
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Collect any new telephony applications for NWAS overflow'
	,GETDATE()

truncate table #ApplicationIDs

insert into #ApplicationIDs
(
	[ApplicationID],
	[SubService]
)
select
	dim.ApplicationID,
	ref.SubService
from SymposiumDW.dbo.DIM_Application (nolock)						dim
	inner join Reference.dbo.ref_ApplicationsToServices (nolock)	ref
		on ref.ApplicationName = dim.Name
where SubService = 'North West NWAS Overflow'

insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Start of NWAS short-calls'
	,GETDATE()

truncate table #ShortCallAnswered

insert into #ShortCallAnswered
(
	[Timestamp],
	[SubService],
	[ShortCallsAnswered]
)
SELECT DISTINCT
	a.[Timestamp]					as [Timestamp],
	tmp.SubService					as [SubService],
	sum(a.[ShortCallsAnswered])		as [ShortCallsAnswered]
FROM [SymposiumDW].[dbo].[FACT_iAgentPerformanceStat] (nolock)			a
	join [SymposiumDW].[dbo].[FACT_iAgentByApplicationStat] (nolock)	ap
		on a.HUBID = ap.hubid 
		and a.[Timestamp] = ap.[Timestamp]
		and a.AgentLogin = ap.agentlogin
	join #ApplicationIDs										tmp 
		on tmp.ApplicationID = ap.ApplicationID
WHERE a.[Timestamp] BETWEEN @Start AND @End
group by a.[Timestamp], tmp.SubService

-- Update #NWAS_Tel_ByIntervals with Short Calls

update #NWAS_Tel_ByIntervals
set #NWAS_Tel_ByIntervals.[mds_ShortCallsAnswered] = #ShortCallAnswered.[ShortCallsAnswered]
from #NWAS_Tel_ByIntervals
	inner join #ShortCallAnswered
		on #NWAS_Tel_ByIntervals.ext_Timestamp = #ShortCallAnswered.[Timestamp]
		and #NWAS_Tel_ByIntervals.ext_SubService = #ShortCallAnswered.SubService
where [mds_Name] = 'NWAS performance NHSD data'

-- Update [mds_NurseWarmTransfersAnswered] and [mds_NurseWarmTransfersAnswerDelay] - Warm Transfered from NWAS

truncate table #tmp_WarmTransfer

insert into #tmp_WarmTransfer
(
	[Timestamp],
	[NurseWarmTransfersAnswered],
	[NurseWarmTransfersAnswerDelay]
)
select distinct
	 iAS.[Timestamp]										as [Timestamp],
	 sum( CallsAnswered )									as NurseWarmTransfersAnswered,
	 sum( CallsAnsweredDelay )								as NurseWarmTransfersAnswerDelay
from [SymposiumDW].[dbo].[FACT_iApplicationStat] (nolock)			iAS
	inner join [SymposiumDW].[dbo].[DIM_Application] (nolock)		dim
		on dim.HUBID = iAS.HUBID
		and dim.ApplicationID = iAS.ApplicationID
	inner join Reference.dbo.ref_ApplicationsToServices (nolock)	ref
		on ref.ApplicationName = dim.Name
where ServiceGroup = '111 Core'
and SubService = 'Warm Transfer Nurse (NWAS)'	-- Limits to NWAS Transfers to NHS Direct Only
and [Timestamp] between @Start AND @End
group by iAS.[Timestamp]
order by iAS.[Timestamp]

-- Update #NWAS_Tel_ByIntervals with Warm Transfers

update #NWAS_Tel_ByIntervals
set #NWAS_Tel_ByIntervals.mds_NurseWarmTransfersAnswered = #tmp_WarmTransfer.NurseWarmTransfersAnswered,
	#NWAS_Tel_ByIntervals.mds_NurseWarmTransfersAnswerDelay = #tmp_WarmTransfer.NurseWarmTransfersAnswerDelay
from #NWAS_Tel_ByIntervals
	inner join #tmp_WarmTransfer
		on #NWAS_Tel_ByIntervals.ext_Timestamp = #tmp_WarmTransfer.[Timestamp]
where #NWAS_Tel_ByIntervals.[mds_Name] = 'NWAS performance NWAS data'

-- Update [mds_NurseWarmTransfersAnswered] and [mds_NurseWarmTransfersAnswerDelay] - Warm Transfered within NHSD

-- Update #NWAS_Tel_ByIntervals with Warm Transfers
-- NOT UPDATED AS NOT RELEVANT FOR NWAS DATA - ADDED BELOW AT NHS DIRECT SIDE.  (SET TO ZERO)

update #NWAS_Tel_ByIntervals
set #NWAS_Tel_ByIntervals.mds_NurseWarmTransfersAnswered = 0,
	#NWAS_Tel_ByIntervals.mds_NurseWarmTransfersAnswerDelay = 0
from #NWAS_Tel_ByIntervals
	inner join #tmp_WarmTransfer
		on #NWAS_Tel_ByIntervals.ext_Timestamp = #tmp_WarmTransfer.[Timestamp]
where #NWAS_Tel_ByIntervals.[mds_Name] = 'NWAS performance NHSD data'

-- FINAL UPDATES

-- Update [ext_Hour]

update #NWAS_Tel_ByIntervals
set #NWAS_Tel_ByIntervals.[ext_Hour] = cal.[intHour]
from #NWAS_Tel_ByIntervals											tmp
	 inner join Reference.dbo.ref_Calendar15MinuteInterval			cal
		on tmp.[ext_Timestamp] = cal.dtmDateTime
where tmp.[ext_Timestamp] between @start and @end

-- update [ext_RollingWeekNo] and [ext_YearMonth]

update #NWAS_Tel_ByIntervals
set #NWAS_Tel_ByIntervals.ext_RollingWeekNo = cal.intRollingWeekNo,
	#NWAS_Tel_ByIntervals.ext_YearMonth = cal.strYearMonth
from #NWAS_Tel_ByIntervals					tmp
	inner join Reference.dbo.ref_Calendar	cal
		on tmp.[mds_Day] = cal.[dtDate]

----------------------------------------------------------------------------------------------------
-- 2.C. NHS Direct Steps
----------------------------------------------------------------------------------------------------

-- NHSD - Step 1a - insert the NHSD elements
--		  with ext_SubService = 'North West 111' i.e. not FCMS or NWAS Overflow or 'North West OOH'
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Get NHSD data and move to 15 minute level'
	,GETDATE()

truncate table #NHSD_Tel_ByIntervals

insert into #NHSD_Tel_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select
	cast( iA.[Timestamp] as date )		as [mds_Day],
	cast( iA.[Timestamp] as time )		as [mds_Time],
	iA.[CallsOffered]					as [mds_CallsOffered],
	iA.[CallsAbandoned]					as [mds_CallsAbandoned],
	iA.[CallsAnswered]					as [mds_CallsAnswered],
	iA.[AbandonedAfter30]				as [mds_AbdOver30],
	iA.[CallsAnswered] - iA.[Ansin60]	as [mds_AnsOver60],
	'NHSD performance NHSD data'		as [mds_Name],
	0									as [mds_ShortCallsAnswered],				-- Updated below.
	0									as [mds_NurseWarmTransfersAnswered],		-- set as "0" rather than "null" to avoid warnings.
	0									as [mds_NurseWarmTransfersAnswerDelay],		-- set as "0" rather than "null" to avoid warnings.
	iA.[Timestamp]						as [ext_Timestamp],
	'North West'						as [ext_Service],
	'North West 111'					as [ext_SubService],
	0									as [ext_Hour],
	null								as [ext_RollingWeekNo],
	null								as [ext_YearMonth]	
from SymposiumDW.dbo.V_iApplicationStat_Derived (nolock)			iA
	inner join Reference.dbo.ref_ApplicationsToServices (nolock)	rfA
		on iA.Name = rfA.ApplicationName
where rfA.SubService = 'North West 111'
and iA.[Timestamp] between @start and @end

union

-- NHSD - Step 1b - insert the NHSD elements
--		  with ext_SubService = 'North West OOH' i.e. not FCMS or NWAS Overflow or 'North West 111'

select
	cast( iA.[Timestamp] as date )		as [mds_Day],
	cast( iA.[Timestamp] as time )		as [mds_Time],
	iA.[CallsOffered]					as [mds_CallsOffered],
	iA.[CallsAbandoned]					as [mds_CallsAbandoned],
	iA.[CallsAnswered]					as [mds_CallsAnswered],
	iA.[AbandonedAfter30]				as [mds_AbdOver30],
	iA.[CallsAnswered] - iA.[Ansin60]	as [mds_AnsOver60],
	'NHSD performance NHSD data'		as [mds_Name],
	0									as [mds_ShortCallsAnswered],				-- Updated below.
	0									as [mds_NurseWarmTransfersAnswered],		-- set as "0" rather than "null" to avoid warnings.
	0									as [mds_NurseWarmTransfersAnswerDelay],		-- set as "0" rather than "null" to avoid warnings.
	iA.[Timestamp]						as [ext_Timestamp],
	'North West'						as [ext_Service],
	'North West OOH'					as [ext_SubService],
	0									as [ext_Hour],
	null								as [ext_RollingWeekNo],
	null								as [ext_YearMonth]
from SymposiumDW.dbo.V_iApplicationStat_Derived (nolock)				iA
	inner join Reference.dbo.ref_ApplicationsToServices (nolock)		rfA
		on iA.Name = rfA.ApplicationName
where rfA.SubService = 'North West OOH'
and iA.[Timestamp] between @start and @end

-- Merge NHSD Steps 1a and 1b derived data together into #NHSD_Tel_ByIntervals

insert into #NHSD_Tel_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select distinct
	[mds_Day],
	[mds_Time],
	sum( [mds_CallsOffered] ),
	sum( [mds_CallsAbandoned] ),
	sum( [mds_CallsAnswered] ),
	sum( [mds_AbdOver30] ),
	sum( [mds_AnsOver60] ),
	[mds_Name],
	0,
	0,
	0,
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	-99,						-- Flag for the merged component records to keep
	null,
	null
from #NHSD_Tel_ByIntervals
group by [mds_Day], [mds_Time], [mds_Name], [ext_Timestamp], [ext_Service], [ext_SubService]
order by [ext_Timestamp], [mds_Name]

-- Delete component records to leave final set row per source
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Get rid of un-needed records'
	,GETDATE()

delete
from #NHSD_Tel_ByIntervals
where [ext_Hour] <> -99

-- Update [mds_ShortCallsAnswered] for Steps 1a + 1b
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Get list of applications for NW111'
	,GETDATE()

truncate table #ApplicationIDs

insert into #ApplicationIDs
(
	[ApplicationID],
	[SubService]
)
select
	dim.ApplicationID,
	ref.SubService
from SymposiumDW.dbo.DIM_Application (nolock)						dim
	inner join Reference.dbo.ref_ApplicationsToServices (nolock)	ref
		on ref.ApplicationName = dim.Name
where SubService in ( 'North West 111', 'North West OOH' )

insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Get short call data'
	,GETDATE()

truncate table #ShortCallAnswered

insert into #ShortCallAnswered
(
	[Timestamp],
	[SubService],
	[ShortCallsAnswered]
)
SELECT DISTINCT
	a.[Timestamp]					as [Timestamp],
	tmp.SubService					as [SubService],
	sum(a.[ShortCallsAnswered])		as [ShortCallsAnswered]
FROM [SymposiumDW].[dbo].[FACT_iAgentPerformanceStat] (nolock)			a
	join [SymposiumDW].[dbo].[FACT_iAgentByApplicationStat] (nolock)	ap
		on a.HUBID = ap.hubid 
		and a.[Timestamp] = ap.[Timestamp]
		and a.AgentLogin = ap.agentlogin
	join #ApplicationIDs (nolock)										tmp 
		on tmp.ApplicationID = ap.ApplicationID
WHERE a.[Timestamp] BETWEEN @Start AND @End
group by a.[Timestamp], tmp.SubService

-- Update #NHSD_Tel_ByIntervals with Short Calls

update #NHSD_Tel_ByIntervals
set #NHSD_Tel_ByIntervals.[mds_ShortCallsAnswered] = #ShortCallAnswered.[ShortCallsAnswered]
from #NHSD_Tel_ByIntervals
	inner join #ShortCallAnswered
		on #NHSD_Tel_ByIntervals.ext_Timestamp = #ShortCallAnswered.[Timestamp]
		and #NHSD_Tel_ByIntervals.ext_SubService = #ShortCallAnswered.SubService
where [mds_Name] = 'NHSD performance NHSD data'

-- NHSD Step 2 - Append the FCMS elements for '111 Overflow Calls' from NHS Direct to FCMS assume 111 dialed
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Append FCMS data to NHSD data'
	,GETDATE()

insert into #NHSD_Tel_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select distinct
	[mds_Day],
	[mds_Time],
	sum( [mds_Calls_Offered] )											as [mds_CallsOffered],						-- Added in v0.52
	sum( [mds_Calls_Abandoned] )										as [mds_CallsAbandoned],
	sum( [mds_Calls_Answered] )											as [mds_CallsAnswered],
	sum( [mds_Calls_Abandoned] )										as [mds_AbdOver30],
	sum( [mds_Calls_Answered] ) - sum( [mds_Overflow_Ans_within_15] )	as [mds_AnsOver60],							-- New and fixed in v0.6
	'NHSD performance FCMS data'										as [mds_Name],
	sum( mds_Short_Calls_Answered )										as [mds_ShortCallsAnswered],
	0																	as [mds_NurseWarmTransfersAnswered],		-- set as "0" rather than "null" to avoid warnings.
	0																	as [mds_NurseWarmTransfersAnswerDelay],		-- set as "0" rather than "null" to avoid warnings.
	[ext_Timestamp]														as [ext_Timestamp],
	[ext_Service]														as [ext_Service],
	[ext_SubService]													as [ext_SubService],
	0																	as [ext_Hour],
	null																as [ext_RollingWeekNo],
	null																as [ext_YearMonth]
from Reports.dbo.rpt_DH111_NW_FCMS_Tel_ByIntervals (nolock)
where [ext_Timestamp] between @start and @end
and [mds_Name] = '111 Overflow Calls'
group by [mds_Day], [mds_Time], [ext_Timestamp], [ext_Service], [ext_SubService]

-- Update [mds_NurseWarmTransfersAnswered] and [mds_NurseWarmTransfersAnswerDelay] - just for NHSD
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Get warm transfer data'
	,GETDATE()

truncate table #tmp_WarmTransfer							-- Added v0.51

insert into #tmp_WarmTransfer
(
	[Timestamp],
	[NurseWarmTransfersAnswered],
	[NurseWarmTransfersAnswerDelay]
)
select distinct
	 iAS.[Timestamp]										as [Timestamp],
	 sum( CallsAnswered )									as NurseWarmTransfersAnswered,
	 sum( CallsAnsweredDelay )								as NurseWarmTransfersAnswerDelay
from [SymposiumDW].[dbo].[FACT_iApplicationStat] (nolock)			iAS
	inner join [SymposiumDW].[dbo].[DIM_Application] (nolock)		dim
		on dim.HUBID = iAS.HUBID
		and dim.ApplicationID = iAS.ApplicationID
	inner join Reference.dbo.ref_ApplicationsToServices (nolock)	ref
		on ref.ApplicationName = dim.Name
where ServiceGroup = '111 Core'
and SubService = 'Warm Transfer Nurse'		-- Excludes the Warm Transfers from FCMS + NWAS + others? - internal NHS Direct
and [Timestamp] between @Start AND @End
group by iAS.[Timestamp]
order by iAS.[Timestamp]

-- Update #NHSD_Tel_ByIntervals with Warm Transfers

update #NHSD_Tel_ByIntervals
set #NHSD_Tel_ByIntervals.mds_NurseWarmTransfersAnswered = #tmp_WarmTransfer.NurseWarmTransfersAnswered,
	#NHSD_Tel_ByIntervals.mds_NurseWarmTransfersAnswerDelay = #tmp_WarmTransfer.NurseWarmTransfersAnswerDelay
from #NHSD_Tel_ByIntervals
	inner join #tmp_WarmTransfer
		on #NHSD_Tel_ByIntervals.ext_Timestamp = #tmp_WarmTransfer.[Timestamp]
where #NHSD_Tel_ByIntervals.[mds_Name] = 'NHSD performance NHSD data'

-- FINAL UPDATES

-- Update [ext_Hour]
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Final set of updates before putting into final temp table'
	,GETDATE()

update #NHSD_Tel_ByIntervals
set #NHSD_Tel_ByIntervals.[ext_Hour] = cal.[intHour]
from #NHSD_Tel_ByIntervals											tmp
	 inner join Reference.dbo.ref_Calendar15MinuteInterval			cal
		on tmp.[ext_Timestamp] = cal.dtmDateTime
where tmp.[ext_Timestamp] between @start and @end

-- update [ext_RollingWeekNo] and [ext_YearMonth]

update #NHSD_Tel_ByIntervals
set #NHSD_Tel_ByIntervals.ext_RollingWeekNo = cal.intRollingWeekNo,
	#NHSD_Tel_ByIntervals.ext_YearMonth = cal.strYearMonth
from #NHSD_Tel_ByIntervals					tmp
	inner join Reference.dbo.ref_Calendar	cal
		on tmp.[mds_Day] = cal.[dtDate]

------------------------------------------------------------------------------------------------------------------------
-- FINAL OUTPUTS TO: rpt_DH111MinimumDataSet_Tel
------------------------------------------------------------------------------------------------------------------------

-- Create Final Records to update to rpt_DH111MinimumDataSet_Tel
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Combine all data into one table'
	,GETDATE()

truncate table #Combined_Tel_ByIntervals

-- Load NHSD performance

insert into #Combined_Tel_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select distinct
	[mds_Day],
	[mds_Time],
	sum( [mds_CallsOffered] ),
	sum( [mds_CallsAbandoned] ),
	sum( [mds_CallsAnswered] ),
	sum( [mds_AbdOver30] ),
	sum( [mds_AnsOver60] ),
	'NHSD performance',
	sum( [mds_ShortCallsAnswered] ),
	sum( [mds_NurseWarmTransfersAnswered] ),
	sum( [mds_NurseWarmTransfersAnswerDelay] ),
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
from #NHSD_Tel_ByIntervals
group by [mds_Day], [mds_Time],
		 [ext_Timestamp], [ext_Service], [ext_SubService],
		 [ext_Hour], [ext_RollingWeekNo], [ext_YearMonth]

-- Load FCMS performance

insert into #Combined_Tel_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select distinct
	[mds_Day],
	[mds_Time],
	sum( [mds_CallsOffered] ),
	sum( [mds_CallsAbandoned] ),
	sum( [mds_CallsAnswered] ),
	sum( [mds_AbdOver30] ),
	sum( [mds_AnsOver60] ),
	'FCMS performance',
	sum( [mds_ShortCallsAnswered] ),
	sum( [mds_NurseWarmTransfersAnswered] ),
	sum( [mds_NurseWarmTransfersAnswerDelay] ),
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
from #FCMS_Tel_ByIntervals
group by [mds_Day], [mds_Time],
		 [ext_Timestamp], [ext_Service], [ext_SubService],
		 [ext_Hour], [ext_RollingWeekNo], [ext_YearMonth]

-- Load NWAS performance

insert into #Combined_Tel_ByIntervals
(
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select distinct
	[mds_Day],
	[mds_Time],
	sum( [mds_CallsOffered] ),
	sum( [mds_CallsAbandoned] ),
	sum( [mds_CallsAnswered] ),
	sum( [mds_AbdOver30] ),
	sum( [mds_AnsOver60] ),
	'NWAS performance',
	sum( [mds_ShortCallsAnswered] ),
	sum( [mds_NurseWarmTransfersAnswered] ),
	sum( [mds_NurseWarmTransfersAnswerDelay] ),
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
from #NWAS_Tel_ByIntervals
group by [mds_Day], [mds_Time],
		 [ext_Timestamp], [ext_Service], [ext_SubService],
		 [ext_Hour], [ext_RollingWeekNo], [ext_YearMonth]

-- delete matching records rpt_DH111MinimumDataSet_Tel
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Remove data from actual table before inserting updated/new data'
	,GETDATE()

delete
from Reports.dbo.rpt_DH111MinimumDataSet_Tel
where ext_Timestamp between @start and @end
and ext_Service = 'North West'

-- Update rpt_DH111MinimumDataSet_Tel_NW111Test
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'Insert updated/new information into actual table'
	,GETDATE()

insert into Reports.dbo.rpt_DH111MinimumDataSet_Tel
(
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
)
select
	[mds_Day],
	[mds_Time],
	[mds_CallsOffered],
	[mds_CallsAbandoned],
	[mds_CallsAnswered],
	[mds_AbdOver30],
	[mds_AnsOver60],
	[mds_Name],
	[mds_ShortCallsAnswered],
	[mds_NurseWarmTransfersAnswered],
	[mds_NurseWarmTransfersAnswerDelay],
	[ext_Timestamp],
	[ext_Service],
	[ext_SubService],
	[ext_Hour],
	[ext_RollingWeekNo],
	[ext_YearMonth]
from #Combined_Tel_ByIntervals
insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'get rid of all leftover temp tables before finish'
	,GETDATE()

drop table #FCMS_Tel_ByIntervals
drop table #NHSD_Tel_ByIntervals
drop table #Combined_Tel_ByIntervals
drop table #ShortCallAnswered
drop table #ApplicationIDs
drop table #tmp_WarmTransfer
DROP TABLE #NWAS_Tel_ByIntervals

insert into reports.dbo.[log_ProcessLog] (
	[ProcessName]
	,[StepName]
	,[StepStartDatetime]
)
select
	'MDS 111 Telephony'
	,'All processing finished - data ready to use'
	,GETDATE()

GO

The logging contained within it (selected from the relevant table), shows nothing unusual (except extended run times).

I can run the SPROC in pieces (which I did the other day) and it runs in about 15 minutes. It's currently been running for 35 minutes with no end in sight. It seems to hang when it hits this point in the log:-
Start of FCMS short-calls
and I can't see why.

Would someone be kind enough to have a look through and see if there's anything odd that could cause it to hang as a stored procedure but let it run OK as a query?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-05 : 09:35:56

Get rid of the if exists ... drop statements at the top.
Log the progress of the sp to a table
I use a table called trace
CREATE TABLE [dbo].[Trace]
(
[Trace_id] [int] IDENTITY(1,1) NOT NULL,
[EventDate] [datetime] NOT NULL default getdate(),
[Entity] [varchar](100) NOT NULL,
[key1] [varchar](100) NULL,
[key2] [varchar](100) NULL,
[key3] [varchar](100) NULL,
[data1] [varchar](max) NULL,
[data2] [varchar](max) NULL,
[data3] [varchar](max) NULL,
[UserName] [varchar](128) NULL default suser_sname()
)
go

alter table Trace add constraint pk_Trace primary key clustered
(Trace_id)
go

then
select @entity = OBJECT_NAME(@@PROCID

select @inserted=@@rowcount
insert trace (entity, key1, key2, data1)
select @entity, @key1, 'insert tbl', 'inserted=' = coalesce(convert(varchar(20),@updated),'null')

Do that after every statement and you will see how long things are taking and where the issue is.
You could just execute each statement individually but this will give you a history in case things go wrong in the future

see
http://www.nigelrivett.net/Products/DWBuilder/TraceTable.html

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-12-05 : 09:50:03

I've got a table that logs all the steps and it only seems to hang in one place (and it's the same place every time).
There's a couple of places where the joins are just "join" so I've changed the "inner join" (don't know if that will help but I don't think it will hurt anything).

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-05 : 10:29:50

>> There's a couple of places where the joins are just "join" so I've changed the "inner join"
No.

>> I've got a table that logs all the steps
No you don't
You have many statements between log entries
You need to find which statement causes the issue. You also need to log the rowcounts to see whether any slowdown is caused by extra data.

Until you do that it's difficult to give any help.
Some things to look at are the range queries - maybe get the IDs then the data in two statements.
Be careful about updating data in temp tables - it's often better to insert into a new table.

You can probably solve the issue yourself once you know what is causing it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-05 : 13:04:51

why so many temp table and I donot see index in temp table? Like Nigel said, you need to specify which one you are having issue?

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-12-07 : 02:25:41

That's the entire point, I don't know where the SP is hanging.
I put in the extra logging yesterday and it ran as it should do (should take about 20 minutes).
I'm going to leave the logging in place (it won't hurt anything) and keep an eye on things.

I'll come back if it stalls again.

Thanks for your help so far.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-07 : 03:18:51

>> I don't know where the SP is hanging.
That's the point of logging - think you realise now that it wasn't adequate.

You must also make sure that nothing ever writes to the log table inside a transaction or that will stop everything from running.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-12-07 : 03:53:47

I do now, so thanks for the prod in the right direction.

I've now got logging after every select into a temp table and every update. I'll see how it runs today and then try and narrow down the bottle-neck from there.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-12-07 : 06:45:13

Just one quick (and possibly stupid) question, would the logging affect the run times?
I've tracked the query for the last 2 days and it hasn't hung up at all.
I can't see any major difference in the row-counts I'd expect to see so was just curious.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-07 : 06:56:26

Shouldn't do.
Could be that a recompile has created a deiffferent query plan - or could just be that something that was causing issues has got away.
At least you can monitor it now.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-12-07 : 08:08:36

I didn't think it would, but thought it worth an ask.
It may well have (I hope) but I'd like to find out what it was (just for curiosity more than anything else).

Thanks for the heads up on the monitoring.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources