Help with improving query

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

Robert,

You can definitely reduce this. There's no reason you need 6 different joins here. Try something like this:

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,

Wrapup1 = SUM(CASE WHEN WrapupData IN ('o 01 Sales Call-Order','o 01-Nuevo Cliente-Pedido') THEN 1 ELSE 0),

Wrapup2 = SUM(CASE WHEN WrapupData IN ('o 02 Sales Call-No Order','o 02-Nuevo Cliente-Sin Pedido' THEN 1 ELSE 0),

Wrapup3 = SUM(CASE WHEN WrapupData IN ('o 03 Managed Account-Order','o 03-Cliente Existente-Pedido') THEN 1 ELSE 0),

Wrapup4 = SUM(CASE WHEN WrapupData IN ('o 04 Managed Account-No Order','o 04-Cliente Existente-No Pedido') THEN 1 ELSE 0),

Wrapup5 = SUM(CASE WHEN WrapupData IN ('o 05 Inbound Service Call','o 05-Devolucíon de Llamada') THEN 1 ELSE 0),

Wrapup6 = SUM(CASE WHEN WrapupData in ('o 06 Other','o 06-Otras') THEN 1 ELSE 0)

FROM Termination_Call_Detail

WHERE  AgentSkillTargetID IN (:AgentSkillTargetID)

and WrapupData LIKE 'o 0%'

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

) TCD

ON TCD.Interval=ASGHH.DateTime AND TCD.AgentSkillTargetID=ASGHH.SkillTargetID

-Jameson