geert.de.vylder (3/6/2013)
Hi all,I adjusted the TestTables file. I included the complete Tussen_Tickets data which is actualy in our table. And I adjusted some other things.
Hope this helps.
You will find it in attachment.
Greetz,
Geert
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)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden