CTE query modification

Question

0.00/5 (No votes)

See more:

Please help me to modify the query in such a way that, it should show the single record for the single Activity. Here the records are showing twice based on the start time and end time because it is taking the end time and making it start time again so the records are showing twice.Also I tried to comment out the Case statement in CTE2 but it is not giving the expected result.So I want it should be the single record without 30 mins split.

SQL

;WITH RCode ( ReasonCodeID, ReasonCode,ReasonText, Description )
AS (
  Select ReasonCodeID, ReasonCode,ReasonText, Description From Reason_Code 
	Union All
	Select 9999,0,'Not Ready-Default', 'Not Ready-System Predefined'
	Union All
	Select 9999,-1,'Agent reinitialized (used if peripheral restarts)', 'Not Ready-System Predefined'
	Union All
	Select 9999,-2,'PG reset the agent, normally due to a PG failure', 'Not Ready-System Predefined'
	Union All
	Select 9999,-3,'An administrator modified the agent''s extension while the agent was logged in', 'Not Ready-System Predefined'
	Union All
	Select 9999,50002,'A CTI OS component failed, causing the agent to be logged out', 'Not Ready-System Predefined'
	Union All
	Select 9999,50003,'Agent was logged out because the Unified CM reported the device out of service', 'Not Ready-System Predefined'
	Union All
	Select 9999,50004,'Agent was logged out due to agent inactivity as configured in agent desk settings', 'Not Ready-System Predefined'
	Union All
	Select 9999,50005,'The Agent will be set to not ready with this code while talking on a call on the Non ACD line', 'Not Ready-System Predefined'
	Union All
	Select 9999,50020,'Agent was logged out when the agent''s skill group dynamically changed on the Administration & Data Server', 'Not Ready-System Predefined'
	Union All
	Select 9999,50040,'Mobile agent was logged out because the call failed', 'Not Ready-System Predefined'
	Union All
	Select 9999,50041,'Mobile agent state changed to Not Ready because the call fails when the mobile agent''s phone line rings busy.', 'Not Ready-System Predefined'
	Union All
	Select 9999,50042,'Mobile agent was logged out because the phone line disconnected while using nailed connection mode', 'Not Ready-System Predefined'
	Union All
	Select 9999,32767,'The agent''s state was changed to Not Ready because the agent did not answer a call and the call was redirected to a different agent or skill group', 'Not Ready-System Predefined'
),
CTE ( RowNum, SerialNum, DateTime, SkillTargetID, Event, Duration, ReasonCode) 
AS ( 
  SELECT
	RowNum = ROW_NUMBER() OVER (PARTITIOn BY SkillTargetID ORDER BY DateTime, SkillTargetID),
    SerialNum = ROW_NUMBER() OVER (PARTITION BY SkillTargetID, Event, Duration ORDER BY DateTime),
    DateTime, SkillTargetID, Event, Duration, ReasonCode
  FROM 
		(Select DateTime,SkillTargetID, Event,
		CASE WHEN (Duration=899 OR Duration=898 OR Duration=901 OR Duration=900) THEN 900 
			 WHEN (Duration=1799 OR Duration=1798 OR Duration=1801 OR Duration=1800) THEN 1800 
			 ELSE Duration end as 'Duration',
		ReasonCode From Agent_Event_Detail Where
		SkillTargetID IN (7969)  		
		And (Convert(varchar(10),DateTime,110)>= '01-31-2018' and convert(varchar(10),DateTime,110) <= '01-31-2018') ) A
),
CTE2 AS (
		Select [Activity], Convert(varchar(10), [Activity], 101) AS [Date],	
				Stuff(right(convert(varchar(30), [Activity], 109), 14), 9, 4, ' ') AS [End Time],
				SkillTargetID, [Agent Name], Event,  [Duration], Z.ReasonCode [Reason Code], R.ReasonText [Reason] From  
		(Select CTE.RowNum, CTE.SerialNum, CTE.DateTime, CTE.SkillTargetID, 
			(Select EnterpriseName From Agent (nolock) Where SkillTargetID=CTE.SkillTargetID) [Agent Name], 
			Event = CASE WHEN CTE.Event = 1 THEN 'Sign-on' WHEN CTE.Event=2 THEN 'Sign-off'  WHEN CTE.Event=3 THEN 'Not-Ready' Else 'Unknown' END, 
		CTE.Duration, CTE.ReasonCode,
		--Problem here	[Activity] = CASE	WHEN (CTE.Event = 3 AND CTE.Duration=1800 and CTE.SerialNum=1) THEN CTE.DateTime
								WHEN (CTE.Event = 3 AND CTE.Duration=900 and CTE.SerialNum=1) THEN CTE.DateTime
								WHEN (CTE.Event = 3 AND CTE.Duration=1800 and CTE.SerialNum<>1) THEN prev.DateTime 
								WHEN (CTE.Event = 3 AND CTE.Duration=900 and CTE.SerialNum<>1) THEN prev.DateTime 
						  ELSE CTE.DateTime
					  END
				FROM CTE 
				LEFT JOIN CTE prev ON prev.RowNum = (CTE.RowNum-CTE.SerialNum)+1 AND prev.SkillTargetID=CTE.SkillTargetID 
		)Z LEFT JOIN RCode R ON R.ReasonCode = Z.ReasonCode  
)
----Select * From CTE2 ORDER BY  Activity, [Agent Name]
Select Activity, Date, Stuff(right(convert(varchar(30), DATEADD(second, -(Sum(Duration)),[Activity]), 109), 14), 9, 4, ' ') AS [Start Time],
[End Time], SkillTargetID, [Agent Name], Event, SUM(Duration) AS [Duration], [Reason Code], Reason From CTE2 
GROUP BY [Activity], Date, [End Time], SkillTargetID, [Agent Name], Event, [Reason Code], Reason
Order By [Agent Name], Activity

What I have tried:

I tried to comment out the Case statement in CTE2 but it is not giving the expected result. and also i would like to understand why the Selef join is used.

Posted 16-Aug-18 18:36pm

Sambit_Sahoo

Updated 17-Aug-18 3:58am

Tomas Takac

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2018-08-17T03:58:00

You have very little chance of us going through that much code without the benefit of sample data. It's also difficult to read because you have use Reserved Words for column names - that is bad practice but if you do use them then surround them with square brackets e.g. [DateTime], [Event]

You said "but it is not giving the expected result" ... but you haven't told us what that expected result is! We can't see your screen, your HDD nor through your eyes so you have to give us enough information

I suggest that you simplify this by removing stuff until you have the bare minimum that still demonstrates the problem - for example the whole of the CTE RCode can be reduced to a single select statement for the purposes of the investigation

If by "I would like to understand why the Self join is used" you are referring to the line

SQL

LEFT JOIN CTE prev ON prev.RowNum = (CTE.RowNum-CTE.SerialNum)+1 AND prev.SkillTargetID=CTE.SkillTargetID

then that is a technique for getting the "previous" record based on particular criteria. There are other techniques discussed in the article Processing Loops in SQL Server[^]

When you want to comment out the CASE in CTE2 you can't just use

SQL

--Problem here	[Activity] = CASE	WHEN (CTE.Event = 3 AND CTE.Duration=1800 and CTE.SerialNum=1) THEN CTE.DateTime
WHEN (CTE.Event = 3 AND CTE.Duration=900 and CTE.SerialNum=1) THEN CTE.DateTime

You have to comment out the entire CASE statement ... i.e. all the way to the END
Like this for example:

SQL

/*Problem here	[Activity] = CASE	WHEN (CTE.Event = 3 AND CTE.Duration=1800 and CTE.SerialNum=1) THEN CTE.DateTime
								WHEN (CTE.Event = 3 AND CTE.Duration=900 and CTE.SerialNum=1) THEN CTE.DateTime
								WHEN (CTE.Event = 3 AND CTE.Duration=1800 and CTE.SerialNum<>1) THEN prev.DateTime 
								WHEN (CTE.Event = 3 AND CTE.Duration=900 and CTE.SerialNum<>1) THEN prev.DateTime 
						  ELSE CTE.DateTime
					  END */

Use the colour coding to help you determine whether or not things have been commented out.

If you post the sample data we need and some expected results into your question (using the Improve question link), then Comment on this solution when you are done, I'll be notified and will try to help further