CTE查询修改 [英] CTE query modification
问题描述
请帮我修改查询，它应该显示单个Activity的单个记录。这里的记录根据开始时间和结束时间显示两次，因为它占用结束时间并再次开始时间，因此记录显示两次。同时我试图在CTE2中注释掉Case语句，但它没有给出我希望它应该是没有30分钟分割的单一记录。
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.
;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
我尝试了什么：
我试图在CTE2中注释掉Case语句，但它没有给出预期的结果。而且我想理解为什么使用Selef连接。
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.
推荐答案
我们很少有机会在没有样本数据的情况下经历那么多代码。它也很难阅读，因为你已经使用保留字作为列名 - 这是不好的做法，但是如果你使用它们，那么用方括号包围它们，例如[DateTime]，[Event]
你说但它没有给出预期的结果......但你没有告诉我们预期的结果是什么！我们无法看到你的屏幕，你的硬盘或通过你的眼睛，所以你必须给我们足够的信息
我建议你通过删除东西来简化这个，直到你有仍然表明问题的最低限度 - 例如，整个CTERCode可以缩减为单个选择语句以进行调查
如果通过我想了解为什么使用自联接，则表示行
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 CTERCodecan 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
LEFT JOIN CTE prev ON prev.RowNum = (CTE.RowNum-CTE.SerialNum)+1 AND prev.SkillTargetID=CTE.SkillTargetID然后，这是一种根据特定条件获取上一个记录的技术。 在SQL Server中处理循环 [
如果你想在CTE2中注释掉CASE，你不能只使用
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
--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您必须注释掉整个CASE 语句 ...即一直到 END
喜欢例如：
You have to comment out the entire CASE statement ... i.e. all the way to the END
Like this for example:
/*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
这篇关于CTE查询修改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
