Hey everyone,
I need some help with improving a query, since it seems like it´s too many characters in it and every night the bottom of it gets lost in Cisco..
Here´s a bit of the query I think (and hope) can be reduced the most.
As you can see, the WrapUp-data needs to be summarized separately.
Can I somehow reduce it to one JOIN instead of 6 separate?
Thanks in advance!
LEFT OUTER JOIN (SELECT
Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID,
Wrapup = Count(WrapupData)
FROM Termination_Call_Detail
WHERE AgentSkillTargetID IN (:AgentSkillTargetID)
and WrapupData IN ('o 01 Sales Call-Order','o 01-Nuevo Cliente-Pedido')
GROUP BY
CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID
) TCD01
ON TCD01.Interval=ASGHH.DateTime AND TCD01.AgentSkillTargetID=ASGHH.SkillTargetID
LEFT OUTER JOIN (SELECT
Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID,
Wrapup = Count(WrapupData)
FROM Termination_Call_Detail
WHERE AgentSkillTargetID IN (:AgentSkillTargetID)
and WrapupData IN ('o 02 Sales Call-No Order','o 02-Nuevo Cliente-Sin Pedido')
GROUP BY
CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID
) TCD02
ON TCD02.Interval=ASGHH.DateTime AND TCD02.AgentSkillTargetID=ASGHH.SkillTargetID
LEFT OUTER JOIN (SELECT
Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID,
Wrapup = Count(WrapupData)
FROM Termination_Call_Detail
WHERE AgentSkillTargetID IN (:AgentSkillTargetID)
and WrapupData IN ('o 03 Managed Account-Order','o 03-Cliente Existente-Pedido')
GROUP BY
CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID
) TCD03
ON TCD03.Interval=ASGHH.DateTime AND TCD03.AgentSkillTargetID=ASGHH.SkillTargetID
LEFT OUTER JOIN (SELECT
Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID,
Wrapup = Count(WrapupData)
FROM Termination_Call_Detail
WHERE AgentSkillTargetID IN (:AgentSkillTargetID)
and WrapupData IN ('o 04 Managed Account-No Order','o 04-Cliente Existente-No Pedido')
GROUP BY
CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID
) TCD04
ON TCD04.Interval=ASGHH.DateTime AND TCD04.AgentSkillTargetID=ASGHH.SkillTargetID
LEFT OUTER JOIN (SELECT
Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID,
Wrapup = Count(WrapupData)
FROM Termination_Call_Detail
WHERE AgentSkillTargetID IN (:AgentSkillTargetID)
and WrapupData IN ('o 05 Inbound Service Call','o 05-Devolucíon de Llamada')
GROUP BY
CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID
) TCD05
ON TCD05.Interval=ASGHH.DateTime AND TCD05.AgentSkillTargetID=ASGHH.SkillTargetID
LEFT OUTER JOIN (SELECT
Interval=CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID,
Wrapup = (Count(WrapupData))
FROM Termination_Call_Detail
WHERE AgentSkillTargetID IN (:AgentSkillTargetID)
and WrapupData in ('o 06 Other','o 06-Otras')
GROUP BY
CASE WHEN (DATEPART(minute,DateTime)<>0 AND DATEPART(minute,DateTime)<>15 AND DATEPART(minute,DateTime)<>30 AND DATEPART(minute,DateTime)<>45) OR DATEPART(second,DateTime)<>0 THEN DATEADD(minute ,((DATEDIFF( minute,0 , DateTime)/ 15)*15 ),0) ELSE DATEADD(minute ,(((DATEDIFF( minute,0 , DateTime)-1)/ 15)*15 ),0) END,
AgentSkillTargetID
) TCD06
ON TCD06.Interval=ASGHH.DateTime AND TCD06.AgentSkillTargetID=ASGHH.SkillTargetID
