ChrisM@Work (3/4/2013)

---

With the added cost of Event_Channel in the matrix table correlating with the inner query, it may be worthwhile splitting the query up, like this:

SELECT *

INTO #Matrix

FROM Tijdsintervallen

CROSS JOIN (

SELECT DISTINCT Ticket_Closed_Date = CAST(Ticket_Closed_DateTime AS DATE)

FROM Tussen_Tickets

) d

CROSS JOIN (

SELECT DISTINCT Event_Channel

FROM Tussen_Tickets

) e

CREATE UNIQUE CLUSTERED INDEX ucx_Everything ON #Matrix (Event_Channel, Ticket_Closed_Date, beginTijdsinterval)

-----------------------------------------------------

SELECT mx.Ticket_Closed_Date, mx.Interval, op.*

FROM #Matrix mx

OUTER APPLY (

SELECT tt.Event_Channel,

DVBIntern = SUM(tt.DVBIntern),

DVBExtern = SUM(tt.DVBExtern),

Tijdsduur = SUM(tt.Tijdsduur),

WrapUp = SUM(tt.WrapUp),

OnHold = SUM(tt.OnHold),

Talk = SUM(tt.Talk),

RowsAggregated = COUNT(*) -- not in spec but handy to have

FROM Tussen_Tickets tt

CROSS APPLY (

SELECT

Ticket_Closed_Time = CAST(tt.Ticket_Closed_DateTime AS TIME(3)),

Ticket_Closed_Date = CAST(tt.Ticket_Closed_DateTime AS DATE)

) ta

WHERE ta.Ticket_Closed_Time BETWEEN mx.beginTijdsinterval AND mx.eindTijdsinterval

AND ta.Ticket_Closed_Date = mx.Ticket_Closed_Date

AND tt.Event_Channel = mx.Event_Channel

GROUP BY ta.Ticket_Closed_Date, tt.Event_Channel

) op

ORDER BY mx.Ticket_Closed_Date, mx.Interval, op.Event_Channel;