Grouping records by time interval

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;

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

-------------------
DROP TABLE #Matrix

SELECT *
INTO #Matrix
FROM Tijdsintervallen
CROSS JOIN (
SELECT DISTINCT Ticket_Closed_Date = CAST(Ticket_Closed_DateTime AS DATE)
FROM Tussen_Tickets
WHERE Ticket_Closed_DateTime IS NOT NULL
) d
CROSS JOIN (
SELECT DISTINCT Event_Channel
FROM Tussen_Tickets
) e
-- (1,180,224 row(s) affected) / 00:00:04

CREATE UNIQUE CLUSTERED INDEX ucx_Everything ON #Matrix (Event_Channel, Ticket_Closed_Date, beginTijdsinterval)
-----------------------------------------------------
-- there's a row in Tussen_Tickets where Ticket_Closed_DateTime is NULL:
DROP TABLE #Tussen_Tickets
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
WHERE tt.Ticket_Closed_DateTime IS NOT NULL
GROUP BY tt.Event_Channel, ta.Ticket_Closed_Date, ta.beginTijdsinterval
-- (24,740 row(s) affected) / 00:00:01

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;
-- (1,180,224 row(s) affected) / 00:00:17 (time is network + display)

ChrisM@Work (3/6/2013)

There's an error in the ddl for the interval table - check the PK.
When providing a script for loading data, always check it first. I know from experience that
INSERT INTO ...
SELECT ... UNION ALL
throws a wobbly on anything more than say 10,000 rows. Break it up into sections of a couple of thousand rows.

Try this:

-------------------
DROP TABLE #Matrix

SELECT *
INTO #Matrix 
FROM Tijdsintervallen 
CROSS JOIN (
	SELECT DISTINCT Ticket_Closed_Date = CAST(Ticket_Closed_DateTime AS DATE) 
	FROM Tussen_Tickets
	WHERE Ticket_Closed_DateTime IS NOT NULL
) d
CROSS JOIN (
	SELECT DISTINCT Event_Channel
	FROM Tussen_Tickets
) e
-- (1,180,224 row(s) affected) / 00:00:04

CREATE UNIQUE CLUSTERED INDEX ucx_Everything ON #Matrix (Event_Channel, Ticket_Closed_Date, beginTijdsinterval)
-----------------------------------------------------
-- there's a row in Tussen_Tickets where Ticket_Closed_DateTime is NULL:
DROP TABLE #Tussen_Tickets
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
WHERE tt.Ticket_Closed_DateTime IS NOT NULL
GROUP BY tt.Event_Channel, ta.Ticket_Closed_Date, ta.beginTijdsinterval 
-- (24,740 row(s) affected) / 00:00:01

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;
-- (1,180,224 row(s) affected) / 00:00:17 (time is network + display)