geert.de.vylder (3/5/2013)

There will be many many rows in the matrix table which don't have a matching ticket, hence null values in columns sourced from the ticket table. However, there shouldn't be any duplicate rows at all. Have you tested for this?

You may get another performance lift from preprocessing the ticket table:

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

tt.Event_Channel,

ta.Ticket_Closed_Date,

ta.beginTijdsinterval,

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

INTO #Tussen_Tickets

FROM Tussen_Tickets tt

CROSS APPLY (

SELECT

beginTijdsinterval = CAST(DATEADD(MINUTE,0-(DATEDIFF(MINUTE,0,tt.Ticket_Closed_DateTime)%15),tt.Ticket_Closed_DateTime) AS TIME(3)),

Ticket_Closed_Date = CAST(tt.Ticket_Closed_DateTime AS DATE)

) ta

GROUP BY tt.Event_Channel, ta.Ticket_Closed_Date, ta.beginTijdsinterval

CREATE UNIQUE CLUSTERED INDEX ucx_Tussen_Tickets_NearlyEverything ON #Tussen_Tickets (Event_Channel, Ticket_Closed_Date, beginTijdsinterval)

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

SELECT mx.Ticket_Closed_Date, mx.Event_Channel, mx.Interval,

tt.DVBIntern, tt.DVBExtern, tt.Tijdsduur, tt.WrapUp, tt.OnHold, tt.Talk, tt.RowsAggregated

FROM #Matrix mx

LEFT JOIN #Tussen_Tickets tt

ON tt.Event_Channel = mx.Event_Channel

AND tt.Ticket_Closed_Date = mx.Ticket_Closed_Date

AND tt.beginTijdsinterval = mx.beginTijdsinterval

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

-- 11520 rows