Модификация запроса CTE

---

Пожалуйста, помогите мне изменить запрос таким образом, чтобы он показывал единственную запись для одного действия. Здесь записи отображаются дважды, основываясь на времени начала и времени окончания, потому что он берет Время окончания и делает его снова начальным, поэтому записи отображаются дважды.Кроме того, я попытался прокомментировать заявление Case в CTE2, но оно не дает ожидаемого результата.Поэтому я хочу, чтобы это была единственная запись без 30-минутного разделения.

;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

Что я уже пробовал:

Я попытался прокомментировать заявление Case в CTE2, но оно не дает ожидаемого результата. а также я хотел бы понять, почему используется Selef join.

---