Query Performance very slow

Motlatjo · Mar 11, 2014

Hi

I have this query that works fine when tested against Pre-Production data, but when i took it to Production it took forever to execute
below is the query

--Start
DECLARE @Agent VARCHAR(25),
@Queue VARCHAR(10),
@Status VARCHAR(15),
@Reason VARCHAR(25),
@Time DATETIME,
@Date DATE,
@TimeStamp BIGINT,
@TotalTime BIGINT,
@Version VARCHAR(5),
@EndTime DATETIME,
@NAgent VARCHAR(25),
@NStartTime DATETIME,
@NEndTime DATETIME,
@NDate DATE,
@NCondition VARCHAR(15),
@NActEvent VARCHAR(25),
@DateFrom VARCHAR(10),
@DateTo VARCHAR(10)

SET @DateFrom = CAST(GETDATE() - 1 AS DATE)
SET @DateTo = CAST(GETDATE() - 1 AS DATE)

--Inserting dayly from AgentTimeDetail
--INSERT INTO AgentTimeDetailNonAdOccurance
SELECT Agent, Queue, Status, Reason, Time, Date, TimeStamp, TotalTime, version, EndTime, 'No' Non_Adherence_Occurance
FROM AgentTimeDetail
WHERE Date BETWEEN @DateFrom AND @DateTo

--An Outer Cursor too loop through Non Adherence Data
DECLARE NonAdherenceCursor CURSOR
FOR
SELECT CREATED, AGENTID, DATEADD(S, DURATION, CREATED), CAST(DAY_TIMESTAMP AS DATE)
FROM AgentAdherence
WHERE CAST(DAY_TIMESTAMP AS DATE) BETWEEN @DateFrom AND @DateTo
--AND AGENTID = 'test-s1032339'

OPEN NonAdherenceCursor
FETCH NEXT FROM NonAdherenceCursor INTO @NStartTime, @NAgent, @NEndTime, @NDate

WHILE @@FETCH_STATUS = 0
BEGIN
--Inner Cursor to loop through the Agent Data
DECLARE AgentTimeDetailCursor CURSOR
FOR
SELECT Agent, Queue, Status, Reason, Time, Date, Timestamp, TotalTime, version, EndTime
FROM AgentTimeDetail
WHERE Date BETWEEN @DateFrom AND @DateTo
--AND Agent = 'test-s1032339'

OPEN AgentTimeDetailCursor
FETCH NEXT FROM AgentTimeDetailCursor INTO @Agent, @Queue, @Status, @Reason, @Time, @Date,
@TimeStamp, @TotalTime, @Version, @EndTime
--Checking for Non Adherence time that bounds agent activities, either partially of Fully
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @NCondition = ''
IF ((@NStartTime < @Time) AND ((@NEndTime > @Time) AND (@NEndTime < @EndTime)))
UPDATE AgentTimeDetailNonAdOccurance
SET Non_Adherence_Occurance = 'Partial',
@NCondition = 'Partial'
WHERE Time = @Time
AND Agent = @NAgent

IF (((@NStartTime > @Time) AND (@NStartTime < @EndTime)) AND (@NEndTime <= @EndTime))
UPDATE AgentTimeDetailNonAdOccurance
SET Non_Adherence_Occurance = 'Partial',
@NCondition = 'Partial'
WHERE Time = @Time
AND Agent = @NAgent

IF ((@NStartTime <= @Time) AND (@NEndTime >= @EndTime))
UPDATE AgentTimeDetailNonAdOccurance
SET Non_Adherence_Occurance = 'Full',
@NCondition = 'Full'
WHERE Time = @Time
AND Agent = @NAgent

IF (((@NStartTime > @Time) AND (@NStartTime < @EndTime)) AND (@NEndTime > @EndTime))
UPDATE AgentTimeDetailNonAdOccurance
SET Non_Adherence_Occurance = 'Partial',
@NCondition = 'Partial'
WHERE Time = @Time
AND Agent = @NAgent

FETCH NEXT FROM AgentTimeDetailCursor INTO @Agent, @Queue, @Status, @Reason, @Time, @Date,
@TimeStamp, @TotalTime, @Version, @EndTime

END
CLOSE AgentTimeDetailCursor
DEALLOCATE AgentTimeDetailCursor

FETCH NEXT FROM NonAdherenceCursor INTO @NStartTime, @NAgent, @NEndTime, @NDate

END
CLOSE NonAdherenceCursor
DEALLOCATE NonAdherenceCursor
----End

Please help with a way i can optimize it

Thank you

sabinUE · Mar 11, 2014

Hi,

First, I don't like this line

Code:

FROM AgentAdherence
[b]WHERE CAST(DAY_TIMESTAMP AS DATE) BETWEEN @DateFrom AND @DateTo[/b]

try to change so that will not cast :

Code:

[b]WHERE DAY_TIMESTAMP > = @DateFrom AND DAY_TIMESTAMP < DATEADD(d,1,@DateTo)[/b]

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

Motlatjo · Mar 11, 2014

Thank you SabinUE

The problem is i am using two Cursors, meaning i go through two tables comparing each record
Is there anyway i can compare records in two tables without using cursors or loops, as this takes a long time to run?

Thank you

sabinUE · Mar 11, 2014

Yes, you can,

show us some values (dummy/real) for your tables and what you want to achives

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

Motlatjo · Mar 11, 2014

Below is the Data

AgentTimeDetail Table data

Agent Status Reason Time Date TotalTime EndTime
s6026 BUSY Ringing 2014-03-07 12:02:13.120 2014-03-07 3000 2014-03-07 12:02:16.033
s6026 BUSY Line Busy 2014-03-07 12:02:16.033 2014-03-07 30000 2014-03-07 12:02:46.413
s6026 BUSY In Call 2014-03-07 12:02:46.413 2014-03-07 14000 2014-03-07 12:03:00.347
s6026 BUSY Wrap Up 2014-03-07 12:03:00.347 2014-03-07 5000 2014-03-07 12:03:05.753
s6026 AVAILABLE Available 2014-03-07 12:03:05.753 2014-03-07 585000 2014-03-07 12:12:50.007
s6026 BUSY Ringing 2014-03-07 12:12:50.007 2014-03-07 2000 2014-03-07 12:12:52.797
s6026 BUSY In Call 2014-03-07 12:12:52.797 2014-03-07 1000 2014-03-07 12:12:53.200
s6026 BUSY In Call 2014-03-07 12:12:53.200 2014-03-07 745000 2014-03-07 12:25:18.303
s6026 LOGGED_OUT Stale Session 2014-03-07 12:25:18.303 2014-03-07 0 2014-03-07 12:25:18.840

AgentAdherence Data
CREATED DAY_TIMESTAMP AGENTID DURATION
2014-03-07 12:11:08.717 2014-03-07 11:59:39.000 s6026 27
2014-03-07 12:12:08.830 2014-03-07 12:11:45.680 s6026 124
2014-03-07 12:13:50.197 2014-03-07 12:12:50.190 s6026 60

Expected Results

Agent Status Reason Time Date TotalTime EndTime Non_Adherence_Occurance
s6026 BUSY In Call 2014-03-07 12:02:46.413 2014-03-07 14000 2014-03-07 12:03:00.347 No
s6026 BUSY Wrap Up 2014-03-07 12:03:00.347 2014-03-07 5000 2014-03-07 12:03:05.753 No
s6026 AVAILABLE Available 2014-03-07 12:03:05.753 2014-03-07 585000 2014-03-07 12:12:50.007 Partial
s6026 BUSY Ringing 2014-03-07 12:12:50.007 2014-03-07 2000 2014-03-07 12:12:52.797 Full
s6026 BUSY In Call 2014-03-07 12:12:52.797 2014-03-07 1000 2014-03-07 12:12:53.200 Full
s6026 BUSY In Call 2014-03-07 12:12:53.200 2014-03-07 745000 2014-03-07 12:25:18.303 Partial
s6026 LOGGED_OUT Stale Session 2014-03-07 12:25:18.303 2014-03-07 0 2014-03-07 12:25:18.840 No
s6026 BUSY Initial 2014-03-07 12:25:18.840 2014-03-07 1291000 2014-03-07 12:46:49.597 No

If the AgentAdherence time bounds the AgentTimeDetail time entirely then ‘Full’ is displayed, if the AgentAdherence starts or ends during the activity, ‘Partial’ will be shown, else 'No' will be shown.

Hope you find this in order

Thank you

sabinUE · Mar 11, 2014

SQL:

;with AgentTimeDetail 
AS
	(select 1 as rID,'s6026' as Agent,'BUSY' as [Status],'Ringing' as Reason,'2014-03-07 12:02:13.120' as [Time],'2014-03-07' as [Date],3000 as TotalTime,'2014-03-07 12:02:16.033' as EndTime union all
	 select 2,'s6026','BUSY','Line Busy','2014-03-07 12:02:16.033','2014-03-07',30000,'2014-03-07 12:02:46.413' union all
	 select 3,'s6026','BUSY','In Call','2014-03-07 12:02:46.413','2014-03-07',14000,'2014-03-07 12:03:00.347' union all
	 select 4,'s6026','BUSY','Wrap Up','2014-03-07 12:03:00.347','2014-03-07',5000,'2014-03-07 12:03:05.753' union all
	 select 5,'s6026','AVAILABLE','Available','2014-03-07 12:03:05.753','2014-03-07',585000,'2014-03-07 12:12:50.007' union all
	 select 6,'s6026','BUSY','Ringing','2014-03-07 12:12:50.007','2014-03-07',2000,'2014-03-07 12:12:52.797' union all
	 select 7,'s6026','BUSY','In Call','2014-03-07 12:12:52.797','2014-03-07',1000,'2014-03-07 12:12:53.200' union  all
	 select 8,'s6026','BUSY','In Call','2014-03-07 12:12:53.200','2014-03-07',745000,'2014-03-07 12:25:18.303' union all
	 select 9,'s6026','LOGGED_OUT','Stale Session','2014-03-07 12:25:18.303','2014-03-07',0,'2014-03-07 12:25:18.840')

,AgentAdherence 
AS
 ( select '2014-03-07 12:11:08.717' as CREATED,'2014-03-07 11:59:39.000' as DAY_TIMESTAMP,'s6026' as AGENTID,27 as DURATION union all
   select '2014-03-07 12:12:08.830','2014-03-07 12:11:45.680','s6026',124 union all
   select '2014-03-07 12:13:50.197','2014-03-07 12:12:50.190','s6026',60)

--If the AgentAdherence time bounds the AgentTimeDetail time entirely then ‘Full’ is displayed, 
--if the AgentAdherence starts or ends during the activity, ‘Partial’ will be shown, 
--else 'No' will be shown.
--FULLL ((@NStartTime <= @Time) AND (@NEndTime >= @EndTime))

--Partial1 (@NStartTime < @Time) AND ((@NEndTime > @Time) AND (@NEndTime < @EndTime)))
--Partial2 (((@NStartTime > @Time) AND (@NStartTime < @EndTime)) AND (@NEndTime <= @EndTime))
--Partial3 (((@NStartTime > @Time) AND (@NStartTime < @EndTime)) AND (@NEndTime > @EndTime))


select 
 rID
,Agent
,[Status]
,[Reason]
,[Time]
,[Date]
,[TotalTime]
,[EndTime]
,NStartTime
,NEndTime
,CASE WHEN [Non_Adherence_Occurance]=2 THEN 'FULL'
	  WHEN [Non_Adherence_Occurance]=1 THEN 'PARTIAL'
	  ELSE 'No' END as [Non_Adherence_Occurance]
from 
(
SELECT 
 rID
,Agent
,[Status]
,[Reason]
,[Time]
,[Date]
,[TotalTime]
,[EndTime]
,A.NStartTime as NStartTime
,A.NEndTime as NEndTime
, A.[Non_Adherence_Occurance]
,row_number() Over(Partition by rID Order by A.[Non_Adherence_Occurance] desc) as rn
FROM AgentTimeDetail ATD
		OUTER APPLY
		(
			SELECT
				CREATED as NStartTime
				,DATEADD(S, DURATION, CREATED) as NEndTime
				,CASE WHEN AAH.CREATED<=ATD.[Time] AND DATEADD(S, DURATION, CREATED)>=ATD.EndTime THEN 2--'FULL' 
					   WHEN AAH.CREATED< ATD.[Time] AND DATEADD(S, DURATION, CREATED)> ATD.[Time] AND DATEADD(S, DURATION, CREATED)<ATD.EndTime THEN 1--'Partial'
					   WHEN AAH.CREATED>ATD.[Time] AND AAH.CREATED< ATD.EndTime AND DATEADD(S, DURATION, CREATED) <=ATD.EndTime THEN 1--'Partial'
					   WHEN AAH.CREATED>ATD.[Time] AND AAH.CREATED< ATD.EndTime AND DATEADD(S, DURATION, CREATED) > ATD.EndTime THEN 1--'Partial'
					   ELSE 0/*'No'*/ ENd as [Non_Adherence_Occurance]
			FROM AgentAdherence AAH
			WHERE ATD.[DATE]=DATEADD(d,DATEDIFF(d,0,AAH.DAY_TIMESTAMP),0)
				 AND ATD.Agent=AAH.AgentID ) A

)Q
WHERE Q.rn=1

output:

SQL:

rID	Agent	Status	Reason	Time	Date	TotalTime	EndTime	NStartTime	NEndTime	Non_Adherence_Occurance
1	s6026	BUSY	Ringing	2014-03-07 12:02:13.120	2014-03-07	3000	2014-03-07 12:02:16.033	2014-03-07 12:11:08.717	2014-03-07 12:11:35.717	No
2	s6026	BUSY	Line Busy	2014-03-07 12:02:16.033	2014-03-07	30000	2014-03-07 12:02:46.413	2014-03-07 12:11:08.717	2014-03-07 12:11:35.717	No
3	s6026	BUSY	In Call	2014-03-07 12:02:46.413	2014-03-07	14000	2014-03-07 12:03:00.347	2014-03-07 12:11:08.717	2014-03-07 12:11:35.717	No
4	s6026	BUSY	Wrap Up	2014-03-07 12:03:00.347	2014-03-07	5000	2014-03-07 12:03:05.753	2014-03-07 12:11:08.717	2014-03-07 12:11:35.717	No
5	s6026	AVAILABLE	Available	2014-03-07 12:03:05.753	2014-03-07	585000	2014-03-07 12:12:50.007	2014-03-07 12:11:08.717	2014-03-07 12:11:35.717	PARTIAL
6	s6026	BUSY	Ringing	2014-03-07 12:12:50.007	2014-03-07	2000	2014-03-07 12:12:52.797	2014-03-07 12:12:08.830	2014-03-07 12:14:12.830	FULL
7	s6026	BUSY	In Call	2014-03-07 12:12:52.797	2014-03-07	1000	2014-03-07 12:12:53.200	2014-03-07 12:12:08.830	2014-03-07 12:14:12.830	FULL
8	s6026	BUSY	In Call	2014-03-07 12:12:53.200	2014-03-07	745000	2014-03-07 12:25:18.303	2014-03-07 12:12:08.830	2014-03-07 12:14:12.830	PARTIAL
9	s6026	LOGGED_OUT	Stale Session	2014-03-07 12:25:18.303	2014-03-07	0	2014-03-07 12:25:18.840	2014-03-07 12:11:08.717	2014-03-07 12:11:35.717	No

I added a row id , so that I have something to make the difference between rows

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

Motlatjo · Mar 11, 2014

Thanks Very much sabinUE

I was busy with this, which is still giving me some issues, i will look at yours now

;WITH agentTimeDetailCTE(atdAgentID, atdStartTime, atdEndDate,adtDate)
AS
(
SELECT Agent, Time, EndTime, Date
FROM AgentTimeDetail
WHERE Date = '2014-03-07'
AND Agent = 's6026'
),
agentAdherenceCTE(adhAgentID, adhStartTime, adhEndDate,adhDate)
AS
(
SELECT AGENTID, CREATED, DATEADD(S, DURATION, CREATED), CAST(DAY_TIMESTAMP AS DATE)
FROM AgentAdherence
WHERE CAST(DAY_TIMESTAMP AS DATE) = '2014-03-07'
AND AgentID = 's6026'
)
SELECT *
FROM(
SELECT DISTINCT atdAgentID AgentID,
atdStartTime Time,
atdEndDate EndDate,
adtDate Date,
CASE
WHEN ((adhStartTime < atdStartTime) AND ((adhEndDate > atdStartTime) AND (adhEndDate < atdEndDate)))
THEN 'Partial'
WHEN (((adhStartTime > atdStartTime) AND (adhStartTime < atdEndDate)) AND (adhEndDate <= atdEndDate))
THEN 'Partial'
WHEN ((adhStartTime <= atdStartTime) AND (adhEndDate >= atdEndDate))
THEN 'Full'
WHEN (((adhStartTime > atdStartTime) AND (adhStartTime < atdEndDate)) AND (adhEndDate > atdEndDate))
THEN 'Partial'
ELSE 'No'
END Condition
FROM agentTimeDetailCTE acte LEFT OUTER JOIN agentAdherenceCTE adcte
ON acte.atdAgentID = adcte.adhAgentID
AND acte.adtDate = adcte.adhDate) qz
--WHERE qz.Condition <> 'No'
ORDER BY qz.Time

Motlatjo · Mar 11, 2014

Morning sabinUE

Can yo please insert the AgentTimeDetail and AgentAdherence data into tables and modify the CTE, im having problems using your query with the tables, it returns duplicates

Thank you

sabinUE · Mar 11, 2014

ok;

I created/populeted the new tbls,

and the script looks the same:

SQL:

select 
 rID
,Agent
,[Status]
,[Reason]
,[Time]
,[Date]
,[TotalTime]
,[EndTime]
,NStartTime
,NEndTime
,CASE WHEN [Non_Adherence_Occurance]=2 THEN 'FULL'
	  WHEN [Non_Adherence_Occurance]=1 THEN 'PARTIAL'
	  ELSE 'No' END as [Non_Adherence_Occurance]
from 
(
SELECT 
 rID
,Agent
,[Status]
,[Reason]
,[Time]
,[Date]
,[TotalTime]
,[EndTime]
,A.NStartTime as NStartTime
,A.NEndTime as NEndTime
, A.[Non_Adherence_Occurance]
,row_number() Over(Partition by rID Order by A.[Non_Adherence_Occurance] desc) as rn
FROM AgentTimeDetail ATD
		OUTER APPLY
		(
			SELECT
				CREATED as NStartTime
				,DATEADD(S, DURATION, CREATED) as NEndTime
				,CASE WHEN AAH.CREATED<=ATD.[Time] AND DATEADD(S, DURATION, CREATED)>=ATD.EndTime THEN 2--'FULL' 
					   WHEN AAH.CREATED< ATD.[Time] AND DATEADD(S, DURATION, CREATED)> ATD.[Time] AND DATEADD(S, DURATION, CREATED)<ATD.EndTime THEN 1--'Partial'
					   WHEN AAH.CREATED>ATD.[Time] AND AAH.CREATED< ATD.EndTime AND DATEADD(S, DURATION, CREATED) <=ATD.EndTime THEN 1--'Partial'
					   WHEN AAH.CREATED>ATD.[Time] AND AAH.CREATED< ATD.EndTime AND DATEADD(S, DURATION, CREATED) > ATD.EndTime THEN 1--'Partial'
					   ELSE 0/*'No'*/ ENd as [Non_Adherence_Occurance]
			FROM AgentAdherence AAH
			WHERE ATD.[DATE]=DATEADD(d,DATEDIFF(d,0,AAH.DAY_TIMESTAMP),0)
				 AND ATD.Agent=AAH.AgentID ) A

)Q
WHERE Q.rn=1

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

Motlatjo · Mar 11, 2014

Thanks

Can you also send me the CTE part because it is missing

Thanks again

Motlatjo · Mar 12, 2014

Below is my query

----START
;WITH agentTimeDetailCTE(atdAgentID, atdStartTime, atdEndDate,atdDate, atdReason, atdStatus, atdTotalTime, atdTimeStamp, atdVersion)
AS
(
SELECT Agent, Time, EndTime, Date, Reason, Status, TotalTime, Timestamp, version
FROM AgentTimeDetail
WHERE Date = '2014-03-07'
AND Agent = 's6026'
),
agentAdherenceCTE(adhAgentID, adhStartTime, adhEndDate,adhDate)
AS
(
SELECT AGENTID, CREATED, DATEADD(S, DURATION, CREATED), CAST(DAY_TIMESTAMP AS DATE)
FROM AgentAdherence
WHERE CAST(DAY_TIMESTAMP AS DATE) = '2014-03-07'
AND AgentID = 's6026'
)
SELECT DISTINCT *
FROM(
SELECT atdAgentID AgentID,
'' Queue,
atdStatus Status,
atdReason Reason,
atdStartTime Time,
atdDate Date,
atdTimeStamp TimeStamp,
atdTotalTime TotalTime,
atdVersion Version,
atdEndDate EndDate,

CASE
WHEN ((adhStartTime < atdStartTime) AND ((adhEndDate > atdStartTime) AND (adhEndDate < atdEndDate)))
THEN 'Partial'
WHEN (((adhStartTime > atdStartTime) AND (adhStartTime < atdEndDate)) AND (adhEndDate <= atdEndDate))
THEN 'Partial'
WHEN ((adhStartTime <= atdStartTime) AND (adhEndDate >= atdEndDate))
THEN 'Full'
WHEN (((adhStartTime > atdStartTime) AND (adhStartTime < atdEndDate)) AND (adhEndDate > atdEndDate))
THEN 'Partial'
ELSE 'No'
END Condition
FROM agentTimeDetailCTE acte FULL OUTER JOIN agentAdherenceCTE adcte
ON acte.atdAgentID = adcte.adhAgentID
AND acte.atdDate = adcte.adhDate
) qz
ORDER BY qz.Time
--OPTION (MAXRECURSION 3)
--WHERE qz.Condition <> 'No'

-----------END
--Returned Duplicates records

AgentID Queue Status Reason Time Date TimeStamp TotalTime Version EndDate Non_Adherence_Occurance
s6026 AVAILABLE Available 2014-03-07 12:03:05.753 2014-03-07 1394186585000 585000 v4 2014-03-07 12:12:50.007 No
s6026 AVAILABLE Available 2014-03-07 12:03:05.753 2014-03-07 1394186585000 585000 v4 2014-03-07 12:12:50.007 Partial
s6026 BUSY Ringing 2014-03-07 12:12:50.007 2014-03-07 1394187170000 2000 v4 2014-03-07 12:12:52.797 Full
s6026 BUSY Ringing 2014-03-07 12:12:50.007 2014-03-07 1394187170000 2000 v4 2014-03-07 12:12:52.797 No
s6026 BUSY In Call 2014-03-07 12:12:52.797 2014-03-07 1394187172000 1000 v4 2014-03-07 12:12:53.200 Full
s6026 BUSY In Call 2014-03-07 12:12:52.797 2014-03-07 1394187172000 1000 v4 2014-03-07 12:12:53.200 No
s6026 BUSY In Call 2014-03-07 12:12:53.200 2014-03-07 1394187173000 745000 v4 2014-03-07 12:25:18.303 No

Please Help

sabinUE · Mar 12, 2014

you need to add a row number

SQL:

row_number() Over(Partition by rID Order by A.[Non_Adherence_Occurance] desc) as rn

and add a where clause:

SQL:

WHERE Q.rn=1

the row_number is use to number each row from your tbl.( where enter the where clause )
you need to Partition by an unique field (i use row ID) u can use timeStamp

also about the case condition:
you need to give some hierary (but not string because you can't right order in string) - i use 2 for full , 1 - partial , 0 = No
this hierary it's use in row_number Order by A.[Non_Adherence_Occurance] desc

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

Motlatjo · Mar 12, 2014

Thanks sabinUE

It is working

Hope this will not our last conversation

Thanks again

sabinUE · Mar 12, 2014

with welcome

SQL:

SELECT
AgentID
,Time
,EndDate
,Date
,CASE WHEN [Condition]=2 THEN 'FULL'
	  WHEN [Condition]=1 THEN 'PARTIAL'
	  ELSE 'No' END as [Condition]

 
FROM 
(
SELECT *
, row_number() Over(Partition by Time Order by Condition desc) as rn

FROM(

SELECT atdAgentID AgentID,
atdStartTime Time, 
atdEndDate EndDate, 
adtDate Date, 
CASE 
	WHEN ((adhStartTime < atdStartTime) AND ((adhEndDate > atdStartTime) AND (adhEndDate < atdEndDate))) 
		THEN 1--'Partial' 
	WHEN (((adhStartTime > atdStartTime) AND (adhStartTime < atdEndDate)) AND (adhEndDate <= atdEndDate)) 
		THEN 1--'Partial' 
	WHEN ((adhStartTime <= atdStartTime) AND (adhEndDate >= atdEndDate)) 
		THEN 2--'Full' 
	WHEN (((adhStartTime > atdStartTime) AND (adhStartTime < atdEndDate)) AND (adhEndDate > atdEndDate)) 
		THEN 1--'Partial' 
	ELSE 0--'No'
END Condition

FROM agentTimeDetailCTE acte LEFT OUTER JOIN agentAdherenceCTE adcte
ON acte.atdAgentID = adcte.adhAgentID
AND acte.adtDate = adcte.adhDate) qz

)ss
Where rn=1

ORDER BY Time

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Query Performance very slow

Motlatjo

Programmer

sabinUE

Programmer

Motlatjo

Programmer

sabinUE

Programmer

Motlatjo

Programmer

sabinUE

Programmer

Motlatjo

Programmer

Motlatjo

Programmer

sabinUE

Programmer

Motlatjo

Programmer

Motlatjo

Programmer

sabinUE

Programmer

Motlatjo

Programmer

sabinUE

Programmer

Similar threads

Part and Inventory Search

Sponsor