InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 21:13:08 GMT20http://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxSELECT MAX(Ticket_Closed_DateTime) FROM Tussen_Tickets-- returns '2013-01-17 10:22:00.000'Thu, 07 Mar 2013 02:46:51 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]ChrisM@Work (3/6/2013)[/b][hr]There's no data in the table for February :-P[code="sql"]-- last month:SELECT MonthStart = DATEADD(month,DATEDIFF(month,0,getdate())-1,0), -- 1st Feb MonthEnd = DATEADD(month,DATEDIFF(month,0,getdate()),0) -- 1st Mar -- month before last:SELECT MonthStart = DATEADD(month,DATEDIFF(month,0,getdate())-2,0), -- 1st Jan MonthEnd = DATEADD(month,DATEDIFF(month,0,getdate())-1,0) -- 1st Feb -- Usage:SELECT *FROM Tussen_TicketsWHERE Ticket_Closed_DateTime >= DATEADD(month,DATEDIFF(month,0,getdate())-1,0) -- greater than or equal to AND Ticket_Closed_DateTime < DATEADD(month,DATEDIFF(month,0,getdate()),0) -- LESS THAN[/code][/quote]I do get 160 rows for february from the Tusen_Tickets table. But this is the Original not aggregated table Tussen_Tickets.I'm getting desperate now, because I dont get any result from the last query with the 2 temp tables.Thu, 07 Mar 2013 02:06:48 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/6/2013)[/b][hr][quote][b]ChrisM@Work (3/6/2013)[/b][hr]I've no idea, Geert. Folks here on SSC will help you with coding decisions; this is more of a business decision. A date filter seems sensible to me but is it appropriate for the consumers of the data? How many days' worth of data are other folks expecting to see?[/quote]We would like to see a month worth of data. Let's say February.[/quote]There's no data in the table for February :-P[code="sql"]-- last month:SELECT MonthStart = DATEADD(month,DATEDIFF(month,0,getdate())-1,0), -- 1st Feb MonthEnd = DATEADD(month,DATEDIFF(month,0,getdate()),0) -- 1st Mar -- month before last:SELECT MonthStart = DATEADD(month,DATEDIFF(month,0,getdate())-2,0), -- 1st Jan MonthEnd = DATEADD(month,DATEDIFF(month,0,getdate())-1,0) -- 1st Feb -- Usage:SELECT *FROM Tussen_TicketsWHERE Ticket_Closed_DateTime >= DATEADD(month,DATEDIFF(month,0,getdate())-1,0) -- greater than or equal to AND Ticket_Closed_DateTime < DATEADD(month,DATEDIFF(month,0,getdate()),0) -- LESS THAN[/code]Wed, 06 Mar 2013 09:32:44 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/6/2013)[/b][hr][quote][b]ChrisM@Work (3/6/2013)[/b][hr]I've no idea, Geert. Folks here on SSC will help you with coding decisions; this is more of a business decision. A date filter seems sensible to me but is it appropriate for the consumers of the data? How many days' worth of data are other folks expecting to see?[/quote]We would like to see a month worth of data. Let's say February.[/quote]How about "all data for last month - which as of today will be February"?Wed, 06 Mar 2013 09:12:10 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]ChrisM@Work (3/6/2013)[/b][hr]I've no idea, Geert. Folks here on SSC will help you with coding decisions; this is more of a business decision. A date filter seems sensible to me but is it appropriate for the consumers of the data? How many days' worth of data are other folks expecting to see?[/quote]We would like to see a month worth of data. Let's say February.Wed, 06 Mar 2013 09:06:30 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/6/2013)[/b][hr][quote][b]ChrisM@Work (3/6/2013)[/b][hr]I meant in terms of potentially reducing the rowcount. Let's call a set from this table containing the same day, all of the intervals, and the same Event_Channel, a [i]frame[/i]. One frame contains 96 rows, one row per interval. There are 9 event channels so there are 864 rows per date. Multiply 864 by the number of dates in the tickets table - 1366 - to yield the number of rows in the matrix table.One possibility is to remove frames which don't have any corresponding rows in the tickets table.[/quote]Sorry,I misunderstood you.Then the best would be to work with a variable of the datetime type and set this variable to a fix date (for example 2013-03-01 YYYY-MM-DD format) or to set it to yesterdays date. And then retreive the [i]frame[/i] for all 9 event channels.[b]Would that be better[/b]?Greetz,Geert[/quote]I've no idea, Geert. Folks here on SSC will help you with coding decisions; this is more of a business decision. A date filter seems sensible to me but is it appropriate for the consumers of the data? How many days' worth of data are other folks expecting to see?Wed, 06 Mar 2013 08:54:38 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]ChrisM@Work (3/6/2013)[/b][hr]I meant in terms of potentially reducing the rowcount. Let's call a set from this table containing the same day, all of the intervals, and the same Event_Channel, a [i]frame[/i]. One frame contains 96 rows, one row per interval. There are 9 event channels so there are 864 rows per date. Multiply 864 by the number of dates in the tickets table - 1366 - to yield the number of rows in the matrix table.One possibility is to remove frames which don't have any corresponding rows in the tickets table.[/quote]Sorry,I misunderstood you.Then the best would be to work with a variable of the datetime type and set this variable to a fix date (for example 2013-03-01 YYYY-MM-DD format) or to set it to yesterdays date. And then retreive the [i]frame[/i] for all 9 event channels.Would that be better?Greetz,GeertWed, 06 Mar 2013 08:47:06 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/6/2013)[/b][hr][quote][b]ChrisM@Work (3/6/2013)[/b][hr]This data set obviously isn't what you were expecting to see, even though it exactly meets your requirements. What would you like to do with it?[/quote]I have to update the data set into another database on a local server in development and on another remote server in production. The other database is used for reporting.Greetz,Geert[/quote]I meant in terms of potentially reducing the rowcount. Let's call a set from this table containing the same day, all of the intervals, and the same Event_Channel, a [i]frame[/i]. One frame contains 96 rows, one row per interval. There are 9 event channels so there are 864 rows per date. Multiply 864 by the number of dates in the tickets table - 1366 - to yield the number of rows in the matrix table.One possibility is to remove frames which don't have any corresponding rows in the tickets table.Wed, 06 Mar 2013 08:24:16 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]ChrisM@Work (3/6/2013)[/b][hr]This data set obviously isn't what you were expecting to see, even though it exactly meets your requirements. What would you like to do with it?[/quote]I have to update the data set into another database on a local server in development and on another remote server in production. The other database is used for reporting.Greetz,GeertWed, 06 Mar 2013 08:01:18 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/6/2013)[/b][hr]...But the columns DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold and Talk are all NULL or zero...[/quote]That sounds about right. The matrix table contains 1,180,224 rows (all dates and event channels found in the tickets table, all intervals ). The aggregated tickets table only contains 24,740 rows, and not much data;[code="sql"]SELECT DVBIntern = SUM(tt.DVBIntern), DVBExtern = SUM(tt.DVBExtern), Tijdsduur = SUM(tt.Tijdsduur), WrapUp = SUM(tt.WrapUp), OnHold = SUM(tt.OnHold), Talk = SUM(tt.Talk)FROM Tussen_Tickets tt[/code][font="Courier New"]DVBIntern DVBExtern Tijdsduur WrapUp OnHold Talk0.........0.........270164....2.567..0......270161.433[/font]This data set obviously isn't what you were expecting to see, even though it exactly meets your requirements. What would you like to do with it?Wed, 06 Mar 2013 07:35:29 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]ChrisM@Work (3/6/2013)[/b][hr]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 ALLthrows a wobbly on anything more than say 10,000 rows. Break it up into sections of a couple of thousand rows.Try this:[code="sql"]-------------------DROP TABLE #MatrixSELECT *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) dCROSS JOIN ( SELECT DISTINCT Event_Channel FROM Tussen_Tickets) e-- (1,180,224 row(s) affected) / 00:00:04CREATE 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_TicketsSELECT 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_TicketsFROM Tussen_Tickets ttCROSS 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) ) taWHERE tt.Ticket_Closed_DateTime IS NOT NULLGROUP BY tt.Event_Channel, ta.Ticket_Closed_Date, ta.beginTijdsinterval -- (24,740 row(s) affected) / 00:00:01CREATE 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.RowsAggregatedFROM #Matrix mxLEFT 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)[/code][/quote]Hi ChrisM@Work,Thank you for the new query. The rows affected are as you mention. But the columns DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold and Talk are all NULL or zero.Greetz,GeertWed, 06 Mar 2013 06:44:05 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/6/2013)[/b][hr]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[/quote]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 ALLthrows a wobbly on anything more than say 10,000 rows. Break it up into sections of a couple of thousand rows.Try this:[code="sql"]-------------------DROP TABLE #MatrixSELECT *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) dCROSS JOIN ( SELECT DISTINCT Event_Channel FROM Tussen_Tickets) e-- (1,180,224 row(s) affected) / 00:00:04CREATE 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_TicketsSELECT 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_TicketsFROM Tussen_Tickets ttCROSS 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) ) taWHERE tt.Ticket_Closed_DateTime IS NOT NULLGROUP BY tt.Event_Channel, ta.Ticket_Closed_Date, ta.beginTijdsinterval -- (24,740 row(s) affected) / 00:00:01CREATE 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.RowsAggregatedFROM #Matrix mxLEFT 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)[/code]Wed, 06 Mar 2013 04:35:07 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxHi 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,GeertWed, 06 Mar 2013 02:15:27 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]b ghanekar (3/6/2013)[/b][hr]I think the window functions should be of help here.Please repost the data I am not able to get the data you have posted.[/quote]The fourth post on this thread has a .txt attachment which contains the ddl and dml and it works just fine. I'd be very interested to see how window functions might be used in a solution.Wed, 06 Mar 2013 01:35:59 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/5/2013)[/b][hr][quote][b]... if the rows are null they are repeated several times....[/quote]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:[code="sql"]SELECT *INTO #Matrix FROM Tijdsintervallen CROSS JOIN ( SELECT DISTINCT Ticket_Closed_Date = CAST(Ticket_Closed_DateTime AS DATE) FROM Tussen_Tickets) dCROSS JOIN ( SELECT DISTINCT Event_Channel FROM Tussen_Tickets) eCREATE 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_TicketsFROM Tussen_Tickets ttCROSS 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) ) taGROUP 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.RowsAggregatedFROM #Matrix mxLEFT 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[/code]Wed, 06 Mar 2013 01:32:08 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxI think the window functions should be of help here.Please repost the data I am not able to get the data you have posted.Wed, 06 Mar 2013 00:33:01 GMTb ghanekarhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]ChrisM@Work (3/4/2013)[/b][hr]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:[code="sql"]SELECT *INTO #Matrix FROM Tijdsintervallen CROSS JOIN ( SELECT DISTINCT Ticket_Closed_Date = CAST(Ticket_Closed_DateTime AS DATE) FROM Tussen_Tickets) dCROSS JOIN ( SELECT DISTINCT Event_Channel FROM Tussen_Tickets) eCREATE UNIQUE CLUSTERED INDEX ucx_Everything ON #Matrix (Event_Channel, Ticket_Closed_Date, beginTijdsinterval)-----------------------------------------------------SELECT mx.Ticket_Closed_Date, mx.Interval, op.* FROM #Matrix mxOUTER 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) opORDER BY mx.Ticket_Closed_Date, mx.Interval, op.Event_Channel;[/code][/quote]Hi ChrisM@Work,Thank you for your help. I did run this query and it generated 1878912 records. Taking a close look, everything is correct but if the rows are null they are repeated several times. In attachment you find the result in a zipped text file.By the way, with the index on the Tussen_Tickets table this query runs in a little more than 1 minute. That's a large improvement.Greetz,GeertTue, 05 Mar 2013 02:58:34 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxHi,I connect to version: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor) Greetz,GeertTue, 05 Mar 2013 01:28:55 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxWith 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:[code="sql"]SELECT *INTO #Matrix FROM Tijdsintervallen CROSS JOIN ( SELECT DISTINCT Ticket_Closed_Date = CAST(Ticket_Closed_DateTime AS DATE) FROM Tussen_Tickets) dCROSS JOIN ( SELECT DISTINCT Event_Channel FROM Tussen_Tickets) eCREATE UNIQUE CLUSTERED INDEX ucx_Everything ON #Matrix (Event_Channel, Ticket_Closed_Date, beginTijdsinterval)-----------------------------------------------------SELECT mx.Ticket_Closed_Date, mx.Interval, op.* FROM #Matrix mxOUTER 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) opORDER BY mx.Ticket_Closed_Date, mx.Interval, op.Event_Channel;[/code]Mon, 04 Mar 2013 09:20:09 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/4/2013)[/b][hr]Hi ChrisM@Work,Yes, event_channel has to be populated, even if there isn't a match in the Tussen_Ticket table. This is because I have to update the results into another table in another database.Thank you very much for your help and assistance.Greetz,Geert[/quote]You're very welcome. Your cooperation is very very much appreciated. Here's a modified query which takes Event_Channel into account as discussed:[code="sql"]SELECT mx.Ticket_Closed_Date, mx.Interval, op.* FROM ( -- create a matrix containing all intervals from Tijdsintervallen and all dates & Event_Channels from Tussen_Tickets SELECT * 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) mxOUTER 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) opORDER BY mx.Ticket_Closed_Date, mx.Interval, op.Event_Channel;[/code]Mon, 04 Mar 2013 09:15:19 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/4/2013)[/b][hr]I'm connecting to SQL Server 2008 R2.[/quote]Run this: SELECT @@VERSIONMon, 04 Mar 2013 09:13:14 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxI'm connecting to SQL Server 2008 R2.Mon, 04 Mar 2013 09:11:42 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxHi ChrisM@Work,Yes, event_channel has to be populated, even if there isn't a match in the Tussen_Ticket table. This is because I have to update the results into another table in another database.Thank you very much for your help and assistance.Greetz,GeertMon, 04 Mar 2013 09:10:43 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxWhat version of SQL Server are you connecting to?Mon, 04 Mar 2013 08:49:03 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/4/2013)[/b][hr]Hi ChrisM@Work,Thank you for your reply. In attachment you will find the executionplan. The Tussen_Tickets table has at this moment 31779 records.The query is almost correct. I only had to change Talk = SUM(tt.OnHold) to Talk = SUM(tt.Talk) in the OUTER APPLY.I also saw that the Event_Channel is not always filled (NULL) and not always correctly sorted.Is that maybe your remark that you saw that my previous post puts column event_channel into the left-hand side along with date, from the Tussen_Ticket table?Greetz,Geert[/quote]Does event_channel have to be populated, even if there isn't a match in the Tussen_Ticket table?Meantime, try this index:CREATE NONCLUSTERED INDEX ix_Ticket_Closed_DateTimeON [dbo].[Tussen_Tickets] ([Ticket_Closed_DateTime])INCLUDE ([Event_Channel],[Tijdsduur],[WrapUp],[OnHold],[DVBIntern],[DVBExtern],[Talk])Mon, 04 Mar 2013 08:44:20 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxHi ChrisM@Work,Thank you for your reply. In attachment you will find the executionplan. The Tussen_Tickets table has at this moment 31779 records.The query is almost correct. I only had to change Talk = SUM(tt.OnHold) to Talk = SUM(tt.Talk) in the OUTER APPLY.I also saw that the Event_Channel is not always filled (NULL) and not always correctly sorted.Is that maybe your remark that you saw that my previous post puts column event_channel into the left-hand side along with date, from the Tussen_Ticket table?Greetz,GeertMon, 04 Mar 2013 08:36:49 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/1/2013)[/b][hr]Hi ChrisM@Work,Thank you for your query and help. But this query needed 13 minutes to run. That's quiet long. How can the performance be improved?Greetz,Geert[/quote]Post the actual execution plan as a .sqlplan file, and I'll tell you. But is it correct? Your last post puts column event_channel into the left-hand side along with date, from the ticket table.Fri, 01 Mar 2013 06:42:00 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxHi ChrisM@Work,Thank you for your query and help. But this query needed 13 minutes to run. That's quiet long. How can the performance be improved?Greetz,GeertFri, 01 Mar 2013 06:36:23 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxHi Cadavre,I attached a file with a script for result table as I would like to have it.The results table is based on the script attached before for the date of 27 Feb 2013 and Event_Channel 293.I hope all is clear and complete now.Greetz,GeertFri, 01 Mar 2013 03:38:32 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxI reckon this does it:[code="sql"]SELECT mx.Ticket_Closed_Date, mx.Interval, op.* FROM ( -- create a matrix containing all intervals from Tijdsintervallen and all dates from Tussen_Tickets SELECT * FROM Tijdsintervallen CROSS JOIN ( SELECT DISTINCT Ticket_Closed_Date = CAST(Ticket_Closed_DateTime AS DATE) FROM Tussen_Tickets ) d) mxOUTER 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.OnHold), RowsAggregated = COUNT(*) -- not in spec but handy to have FROM Tussen_Tickets tt CROSS APPLY ( SELECT Ticket_Closed_Time = CAST(Ticket_Closed_DateTime AS TIME(3)), Ticket_Closed_Date = CAST(Ticket_Closed_DateTime AS DATE) ) ta WHERE ta.Ticket_Closed_Time BETWEEN mx.beginTijdsinterval AND mx.eindTijdsinterval AND Ticket_Closed_Date = mx.Ticket_Closed_Date GROUP BY ta.Ticket_Closed_Date, tt.Event_Channel) opORDER BY mx.Ticket_Closed_Date, mx.Interval, op.Event_Channel[/code]Fri, 01 Mar 2013 03:36:32 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (3/1/2013)[/b][hr]Hi Cadavre,Thank you for your help and assistance.Can you explain me what happens per step. I ask this because the result does not group the columns per interval and not all the 96 intervals (quarters per hour) are represented in the result.Your explanation will help me to improve the query.Thank you very much for what you do for me.Greetz,Geert[/quote]I have no idea what you're asking me.Script out your expected results based on your sample data and I'll see if I can work out what you want.Fri, 01 Mar 2013 02:21:47 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxHi Cadavre,Thank you for your help and assistance.Can you explain me what happens per step. I ask this because the result does not group the columns per interval and not all the 96 intervals (quarters per hour) are represented in the result.Your explanation will help me to improve the query.Thank you very much for what you do for me.Greetz,GeertFri, 01 Mar 2013 01:46:08 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (2/28/2013)[/b][hr]Hi all,My appologies.I had the file with the test data ready, but forgot to attach it to the post.Here it is.Thank you very much for your help.Greetz,Geert[/quote]I had to fix your script a little, so make sure you test it before posting next time ;-)Anyway, without your expected result-set it is difficult to be sure what you want. Here's my best guess: -[code="sql"]SELECT d.*FROM #Tussen_TicketsOUTER APPLY (SELECT Interval AS StartInterval FROM #Tijdsintervallen WHERE CAST(Ticket_Accepted_DateTime AS TIME) >= beginTijdsinterval AND CAST(Ticket_Accepted_DateTime AS TIME) <= eindTijdsinterval ) bOUTER APPLY (SELECT Interval AS EndInterval FROM #Tijdsintervallen WHERE CAST(Ticket_Closed_DateTime AS TIME) >= beginTijdsinterval AND CAST(Ticket_Closed_DateTime AS TIME) <= eindTijdsinterval ) cCROSS APPLY (SELECT DISTINCT [Date], Interval, Event_Channel, DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold, Talk, Ticket_Accepted_DateTime, Ticket_Closed_DateTime FROM (SELECT CAST(CAST(Ticket_Accepted_DateTime AS DATE) AS DATETIME) AS [Date], StartInterval AS Interval, Event_Channel, DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold, Talk, Ticket_Accepted_DateTime, Ticket_Closed_DateTime UNION ALL SELECT CAST(CAST(Ticket_Accepted_DateTime AS DATE) AS DATETIME) AS [Date], EndInterval AS Interval, Event_Channel, DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold, Talk, Ticket_Accepted_DateTime, Ticket_Closed_DateTime )ua )d;[/code]Which produces: -[code="plain"]Date Interval Event_Channel DVBIntern DVBExtern Tijdsduur WrapUp OnHold Talk Ticket_Accepted_DateTime Ticket_Closed_DateTime----------------------- -------------------------------------------------- -------------------- ----------- ----------- ----------- ---------------------- ---------------------- ---------------------- ------------------------ -----------------------2013-01-29 00:00:00.000 12:30-12:45 295 0 0 46 0 0 46 2013-01-29 12:43:00.000 2013-01-29 12:44:00.0002013-01-29 00:00:00.000 15:45-16:00 295 0 0 31 0 0 31 2013-01-29 15:49:00.000 2013-01-29 15:50:00.0002013-01-31 00:00:00.000 14:30-14:45 4000 0 0 43 0 0 43 2013-01-31 14:41:00.000 2013-01-31 14:42:00.0002013-01-31 00:00:00.000 15:00-15:15 VulIn3 0 0 30 0 0 30 2013-01-31 15:14:00.000 2013-01-31 15:14:00.0002013-01-31 00:00:00.000 16:30-16:45 VulIn3 0 0 813 0 0 813 2013-01-31 16:39:00.000 2013-01-31 16:53:00.0002013-01-31 00:00:00.000 16:45-17:00 VulIn3 0 0 813 0 0 813 2013-01-31 16:39:00.000 2013-01-31 16:53:00.0002013-02-01 00:00:00.000 11:15-11:30 VulIn1 0 0 24 0 0 24 2013-02-01 11:17:00.000 2013-02-01 11:17:00.0002013-02-01 00:00:00.000 13:45-14:00 VulIn1 1 0 79 10.052 8.348 60.6 2013-02-01 13:54:00.000 2013-02-01 13:56:00.0002013-02-01 00:00:00.000 14:00-14:15 VulIn1 0 0 87 9.178 5.606 72.216 2013-02-01 14:01:00.000 2013-02-01 14:03:00.0002013-02-01 00:00:00.000 14:00-14:15 293 0 0 72 0 6.776 65.224 2013-02-01 14:03:00.000 2013-02-01 14:05:00.0002013-02-01 00:00:00.000 14:30-14:45 293 0 0 148 0 0 148 2013-02-01 14:36:00.000 2013-02-01 14:38:00.0002013-02-01 00:00:00.000 14:45-15:00 VulIn1 0 0 56 0 0 56 2013-02-01 14:51:00.000 2013-02-01 14:52:00.0002013-02-01 00:00:00.000 14:45-15:00 293 0 0 32 0 0 32 2013-02-01 14:52:00.000 2013-02-01 14:52:00.0002013-02-01 00:00:00.000 14:45-15:00 VulIn3 0 0 113 0 0 113 2013-02-01 14:56:00.000 2013-02-01 14:58:00.0002013-02-01 00:00:00.000 15:00-15:15 293 0 0 29 0 0 29 2013-02-01 15:05:00.000 2013-02-01 15:06:00.0002013-02-01 00:00:00.000 15:00-15:15 293 0 0 61 0 0 61 2013-02-01 15:08:00.000 2013-02-01 15:09:00.0002013-02-01 00:00:00.000 16:00-16:15 293 0 0 281 0 0 281 2013-02-01 16:12:00.000 2013-02-01 16:16:00.0002013-02-01 00:00:00.000 16:15-16:30 293 0 0 281 0 0 281 2013-02-01 16:12:00.000 2013-02-01 16:16:00.0002013-02-01 00:00:00.000 16:15-16:30 293 0 0 789 0 0 789 2013-02-01 16:18:00.000 2013-02-01 16:31:00.0002013-02-01 00:00:00.000 16:30-16:45 293 0 0 789 0 0 789 2013-02-01 16:18:00.000 2013-02-01 16:31:00.0002013-02-03 00:00:00.000 18:45-19:00 VulIn3 0 0 20 0 0 20 2013-02-03 18:48:00.000 2013-02-03 18:49:00.0002013-02-03 00:00:00.000 18:45-19:00 4000 0 0 45 0 0 45 2013-02-03 18:49:00.000 2013-02-03 18:50:00.0002013-02-04 00:00:00.000 09:15-09:30 4000 0 0 15 0 0 15 2013-02-04 09:16:00.000 2013-02-04 09:16:00.0002013-02-04 00:00:00.000 09:15-09:30 4000 0 0 24 0 0 24 2013-02-04 09:19:00.000 2013-02-04 09:19:00.0002013-02-04 00:00:00.000 09:15-09:30 4000 0 0 23 0 0 23 2013-02-04 09:22:00.000 2013-02-04 09:22:00.0002013-02-04 00:00:00.000 09:15-09:30 293 0 0 26 0 0 26 2013-02-04 09:22:00.000 2013-02-04 09:23:00.0002013-02-04 00:00:00.000 09:15-09:30 298 0 0 15 0 0 15 2013-02-04 09:23:00.000 2013-02-04 09:23:00.0002013-02-04 00:00:00.000 09:15-09:30 295 0 0 38 0 0 38 2013-02-04 09:23:00.000 2013-02-04 09:24:00.0002013-02-04 00:00:00.000 09:15-09:30 295 0 0 47 0 0 47 2013-02-04 09:24:00.000 2013-02-04 09:25:00.0002013-02-04 00:00:00.000 09:45-10:00 293 0 0 1001 0 0 1001 2013-02-04 09:45:00.000 2013-02-04 10:02:00.0002013-02-04 00:00:00.000 10:00-10:15 293 0 0 1001 0 0 1001 2013-02-04 09:45:00.000 2013-02-04 10:02:00.0002013-02-04 00:00:00.000 10:00-10:15 293 0 0 246 0 0 246 2013-02-04 10:09:00.000 2013-02-04 10:13:00.0002013-02-04 00:00:00.000 10:15-10:30 293 0 0 56 0 0 56 2013-02-04 10:21:00.000 2013-02-04 10:22:00.0002013-02-04 00:00:00.000 10:15-10:30 293 0 0 210 0 0 210 2013-02-04 10:26:00.000 2013-02-04 10:29:00.0002013-02-04 00:00:00.000 10:30-10:45 293 0 0 87 0 0 87 2013-02-04 10:34:00.000 2013-02-04 10:35:00.0002013-02-06 00:00:00.000 15:15-15:30 293 0 0 332 0 0 332 2013-02-06 15:28:00.000 2013-02-06 15:34:00.0002013-02-06 00:00:00.000 15:30-15:45 293 0 0 332 0 0 332 2013-02-06 15:28:00.000 2013-02-06 15:34:00.0002013-02-08 00:00:00.000 15:15-15:30 VulIn1 0 0 23 0 0 23 2013-02-08 15:25:00.000 2013-02-08 15:25:00.0002013-02-08 00:00:00.000 16:45-17:00 297 0 0 37 0 0 37 2013-02-08 16:54:00.000 2013-02-08 16:55:00.0002013-02-18 00:00:00.000 11:30-11:45 293 0 0 19 0 0 19 2013-02-18 11:39:00.000 2013-02-18 11:39:00.0002013-02-18 00:00:00.000 11:30-11:45 293 0 0 6 0 0 6 2013-02-18 11:42:00.000 2013-02-18 11:42:00.0002013-02-18 00:00:00.000 11:30-11:45 293 0 0 5 0 0 5 2013-02-18 11:43:00.000 2013-02-18 11:43:00.0002013-02-18 00:00:00.000 11:45-12:00 293 0 0 7 0 0 7 2013-02-18 11:52:00.000 2013-02-18 11:52:00.0002013-02-18 00:00:00.000 11:45-12:00 293 0 0 7 0 0 7 2013-02-18 11:56:00.000 2013-02-18 11:56:00.0002013-02-19 00:00:00.000 10:45-11:00 293 0 0 73 0 0 73 2013-02-19 10:53:00.000 2013-02-19 10:54:00.0002013-02-19 00:00:00.000 10:45-11:00 VulIn1 0 0 13 0 0 13 2013-02-19 10:55:00.000 2013-02-19 10:55:00.0002013-02-19 00:00:00.000 11:15-11:30 4000 0 0 729 0 0 729 2013-02-19 11:20:00.000 2013-02-19 11:32:00.0002013-02-19 00:00:00.000 11:30-11:45 4000 0 0 729 0 0 729 2013-02-19 11:20:00.000 2013-02-19 11:32:00.0002013-02-19 00:00:00.000 11:30-11:45 VulIn3 0 0 142 0 0 142 2013-02-19 11:42:00.000 2013-02-19 11:44:00.0002013-02-19 00:00:00.000 11:45-12:00 4001 0 0 75 0 0 75 2013-02-19 11:45:00.000 2013-02-19 11:47:00.0002013-02-19 00:00:00.000 11:45-12:00 4001 0 0 120 0 0 120 2013-02-19 11:49:00.000 2013-02-19 11:51:00.0002013-02-19 00:00:00.000 11:45-12:00 VulIn1 0 0 207 0 0 207 2013-02-19 11:52:00.000 2013-02-19 11:56:00.0002013-02-19 00:00:00.000 11:45-12:00 293 0 0 83 0 0 83 2013-02-19 11:56:00.000 2013-02-19 11:57:00.0002013-02-19 00:00:00.000 11:45-12:00 298 0 0 10 0 0 10 2013-02-19 11:58:00.000 2013-02-19 11:58:00.0002013-02-19 00:00:00.000 11:45-12:00 4000 0 0 132 0 0 132 2013-02-19 11:58:00.000 2013-02-19 12:00:00.0002013-02-19 00:00:00.000 12:00-12:15 4000 0 0 132 0 0 132 2013-02-19 11:58:00.000 2013-02-19 12:00:00.0002013-02-19 00:00:00.000 13:45-14:00 4000 0 0 48 0 0 48 2013-02-19 13:52:00.000 2013-02-19 13:53:00.0002013-02-19 00:00:00.000 13:45-14:00 4001 0 1 398 5.678 0 392.322 2013-02-19 13:53:00.000 2013-02-19 14:00:00.0002013-02-19 00:00:00.000 14:00-14:15 4001 0 1 398 5.678 0 392.322 2013-02-19 13:53:00.000 2013-02-19 14:00:00.0002013-02-25 00:00:00.000 10:30-10:45 4001 0 0 19 0 0 19 2013-02-25 10:39:00.000 2013-02-25 10:39:00.0002013-02-25 00:00:00.000 10:30-10:45 4001 0 0 35 0 0 35 2013-02-25 10:42:00.000 2013-02-25 10:42:00.0002013-02-25 00:00:00.000 11:15-11:30 293 0 0 34 0 0 34 2013-02-25 11:16:00.000 2013-02-25 11:16:00.0002013-02-25 00:00:00.000 14:30-14:45 4001 0 0 253 0 0 253 2013-02-25 14:34:00.000 2013-02-25 14:38:00.0002013-02-25 00:00:00.000 16:30-16:45 4001 0 0 643 0 0 643 2013-02-25 16:32:00.000 2013-02-25 16:43:00.0002013-02-25 00:00:00.000 16:30-16:45 4000 0 0 124 2.371 0 121.629 2013-02-25 16:38:00.000 2013-02-25 16:40:00.0002013-02-25 00:00:00.000 16:30-16:45 4000 0 0 18 3.792 0 14.208 2013-02-25 16:40:00.000 2013-02-25 16:40:00.0002013-02-25 00:00:00.000 16:30-16:45 4000 0 0 105 76.548 0 28.452 2013-02-25 16:41:00.000 2013-02-25 16:43:00.0002013-02-25 00:00:00.000 16:30-16:45 4000 0 0 60 5.26 0 54.74 2013-02-25 16:43:00.000 2013-02-25 16:44:00.0002013-02-25 00:00:00.000 16:30-16:45 4000 0 0 36 9.385 0 26.615 2013-02-25 16:44:00.000 2013-02-25 16:45:00.0002013-02-25 00:00:00.000 16:45-17:00 4000 0 0 36 9.385 0 26.615 2013-02-25 16:44:00.000 2013-02-25 16:45:00.0002013-02-25 00:00:00.000 16:45-17:00 4000 0 0 55 8.464 0 46.536 2013-02-25 16:45:00.000 2013-02-25 16:46:00.0002013-02-25 00:00:00.000 16:45-17:00 4000 0 0 15 6.775 0 8.225 2013-02-25 16:46:00.000 2013-02-25 16:46:00.0002013-02-25 00:00:00.000 16:45-17:00 4000 0 0 15 11.917 0 3.083 2013-02-25 16:47:00.000 2013-02-25 16:47:00.0002013-02-25 00:00:00.000 16:45-17:00 4000 0 0 23 8.963 0 14.037 2013-02-25 16:50:00.000 2013-02-25 16:50:00.0002013-02-25 00:00:00.000 16:45-17:00 4000 0 0 15 6.145 0 8.855 2013-02-25 16:50:00.000 2013-02-25 16:50:00.0002013-02-26 00:00:00.000 08:45-09:00 293 0 0 81 0 0 81 2013-02-26 08:46:00.000 2013-02-26 08:47:00.0002013-02-26 00:00:00.000 09:45-10:00 293 0 0 23 0 0 23 2013-02-26 09:48:00.000 2013-02-26 09:49:00.0002013-02-26 00:00:00.000 09:45-10:00 294 0 0 740 0 0 740 2013-02-26 09:49:00.000 2013-02-26 10:01:00.0002013-02-26 00:00:00.000 10:00-10:15 294 0 0 740 0 0 740 2013-02-26 09:49:00.000 2013-02-26 10:01:00.0002013-02-26 00:00:00.000 10:45-11:00 123 0 0 130 0 0 130 2013-02-26 10:58:00.000 2013-02-26 11:00:00.0002013-02-26 00:00:00.000 11:00-11:15 123 0 0 130 0 0 130 2013-02-26 10:58:00.000 2013-02-26 11:00:00.0002013-02-26 00:00:00.000 11:00-11:15 293 0 0 45 0 0 45 2013-02-26 11:01:00.000 2013-02-26 11:02:00.0002013-02-26 00:00:00.000 11:00-11:15 293 0 0 167 0 0 167 2013-02-26 11:02:00.000 2013-02-26 11:05:00.0002013-02-26 00:00:00.000 11:00-11:15 293 0 0 364 0 0 364 2013-02-26 11:05:00.000 2013-02-26 11:11:00.0002013-02-26 00:00:00.000 11:00-11:15 VulIn1 0 0 743 0 0 743 2013-02-26 11:14:00.000 2013-02-26 11:26:00.0002013-02-26 00:00:00.000 11:15-11:30 VulIn1 0 0 743 0 0 743 2013-02-26 11:14:00.000 2013-02-26 11:26:00.0002013-02-26 00:00:00.000 11:15-11:30 293 0 0 1013 0 0 1013 2013-02-26 11:26:00.000 2013-02-26 11:43:00.0002013-02-26 00:00:00.000 11:30-11:45 293 0 0 1013 0 0 1013 2013-02-26 11:26:00.000 2013-02-26 11:43:00.0002013-02-26 00:00:00.000 11:45-12:00 4000 0 0 26 0 0 26 2013-02-26 11:45:00.000 2013-02-26 11:45:00.0002013-02-26 00:00:00.000 11:45-12:00 4000 0 0 20 6.99 0 13.01 2013-02-26 11:45:00.000 2013-02-26 11:46:00.0002013-02-26 00:00:00.000 12:00-12:15 4000 0 0 97 85.372 0 11.628 2013-02-26 12:13:00.000 2013-02-26 12:14:00.0002013-02-26 00:00:00.000 12:15-12:30 4000 0 0 113 71.516 0 41.484 2013-02-26 12:19:00.000 2013-02-26 12:21:00.0002013-02-26 00:00:00.000 13:30-13:45 4000 0 0 113 4.665 0 108.335 2013-02-26 13:39:00.000 2013-02-26 13:41:00.0002013-02-26 00:00:00.000 13:30-13:45 4001 0 0 12 7.504 0 4.496 2013-02-26 13:41:00.000 2013-02-26 13:41:00.0002013-02-26 00:00:00.000 13:30-13:45 4001 0 0 59 46.303 0 12.697 2013-02-26 13:41:00.000 2013-02-26 13:42:00.0002013-02-26 00:00:00.000 13:45-14:00 VulIn1 0 0 28 0 0 28 2013-02-26 13:48:00.000 2013-02-26 13:49:00.0002013-02-26 00:00:00.000 14:00-14:15 4000 0 0 74 0 0 74 2013-02-26 14:02:00.000 2013-02-26 14:03:00.0002013-02-26 00:00:00.000 14:00-14:15 123 0 0 31 0 0 31 2013-02-26 14:03:00.000 2013-02-26 14:04:00.0002013-02-26 00:00:00.000 14:00-14:15 298 0 0 23 0 0 23 2013-02-26 14:04:00.000 2013-02-26 14:04:00.0002013-02-26 00:00:00.000 14:30-14:45 4000 0 0 108 0 0 108 2013-02-26 14:30:00.000 2013-02-26 14:32:00.0002013-02-26 00:00:00.000 14:45-15:00 4000 0 0 2828 0 0 2828 2013-02-26 14:50:00.000 2013-02-26 15:37:00.0002013-02-26 00:00:00.000 15:30-15:45 4000 0 0 2828 0 0 2828 2013-02-26 14:50:00.000 2013-02-26 15:37:00.0002013-02-26 00:00:00.000 16:00-16:15 293 0 0 1363 0 0 1363 2013-02-26 16:02:00.000 2013-02-26 16:25:00.0002013-02-26 00:00:00.000 16:15-16:30 293 0 0 1363 0 0 1363 2013-02-26 16:02:00.000 2013-02-26 16:25:00.0002013-02-26 00:00:00.000 16:00-16:15 298 0 0 9 0 0 9 2013-02-26 16:07:00.000 2013-02-26 16:07:00.0002013-02-27 00:00:00.000 09:00-09:15 4000 0 0 25 0 0 25 2013-02-27 09:13:00.000 2013-02-27 09:14:00.0002013-02-27 00:00:00.000 09:45-10:00 293 0 0 98 0 0 98 2013-02-27 09:52:00.000 2013-02-27 09:53:00.0002013-02-27 00:00:00.000 10:15-10:30 293 0 0 36 0 0 36 2013-02-27 10:21:00.000 2013-02-27 10:21:00.0002013-02-27 00:00:00.000 10:30-10:45 293 0 0 14 0 0 14 2013-02-27 10:38:00.000 2013-02-27 10:39:00.0002013-02-27 00:00:00.000 10:30-10:45 293 0 0 224 0 0 224 2013-02-27 10:40:00.000 2013-02-27 10:44:00.0002013-02-27 00:00:00.000 10:45-11:00 293 0 0 39 0 0 39 2013-02-27 10:47:00.000 2013-02-27 10:48:00.0002013-02-27 00:00:00.000 10:45-11:00 293 0 0 51 0 0 51 2013-02-27 10:52:00.000 2013-02-27 10:52:00.0002013-02-27 00:00:00.000 11:45-12:00 4001 0 1 51 27.416 0 23.584 2013-02-27 11:50:00.000 2013-02-27 11:51:00.0002013-02-27 00:00:00.000 11:45-12:00 4001 0 0 27 3.124 0 23.876 2013-02-27 11:52:00.000 2013-02-27 11:52:00.0002013-02-27 00:00:00.000 11:45-12:00 4001 0 1 19 4.893 0 14.107 2013-02-27 11:52:00.000 2013-02-27 11:52:00.0002013-02-27 00:00:00.000 11:45-12:00 4001 0 0 4055 4044.14 0 10.857 2013-02-27 11:53:00.000 2013-02-27 13:00:00.0002013-02-27 00:00:00.000 13:00-13:15 4001 0 0 4055 4044.14 0 10.857 2013-02-27 11:53:00.000 2013-02-27 13:00:00.0002013-02-27 00:00:00.000 13:00-13:15 4001 0 0 135 5.538 0 129.462 2013-02-27 13:07:00.000 2013-02-27 13:09:00.0002013-02-27 00:00:00.000 13:15-13:30 4001 0 0 58 8.149 35.52 14.331 2013-02-27 13:21:00.000 2013-02-27 13:22:00.0002013-02-27 00:00:00.000 13:15-13:30 4001 0 0 21 6.392 14.527 0.081 2013-02-27 13:22:00.000 2013-02-27 13:22:00.0002013-02-27 00:00:00.000 13:30-13:45 4001 0 0 131 0.771 0 130.229 2013-02-27 13:30:00.000 2013-02-27 13:32:00.0002013-02-27 00:00:00.000 13:30-13:45 4001 0 0 225 198.061 0 26.939 2013-02-27 13:32:00.000 2013-02-27 13:36:00.0002013-02-27 00:00:00.000 13:30-13:45 293 0 0 106 0 0 106 2013-02-27 13:43:00.000 2013-02-27 13:44:00.0002013-02-27 00:00:00.000 13:45-14:00 4001 0 0 73 0 0 73 2013-02-27 13:48:00.000 2013-02-27 13:49:00.000[/code]Thu, 28 Feb 2013 09:49:08 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxHi all,My appologies.I had the file with the test data ready, but forgot to attach it to the post.Here it is.Thank you very much for your help.Greetz,GeertThu, 28 Feb 2013 09:27:19 GMTgeert.de.vylderhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspx[quote][b]geert.de.vylder (2/28/2013)[/b][hr]Hi everyone,I have been searching for a few days now to try to solve a problem, but without success. What I did find on the net wasn't what I want.We have a large DB with a lot of tables. From those tables I have to use 2 tables in my query.The query has to group all the records from the table 'Tussen_Tickets' per time interval of 15 minutes based on the column Ticket_Closed_DateTime and group it also per Event_Channel which is a column in the table. The intervals can be found in the second table with the name 'Tijdsintervallen'. The result has also to represent all the intervals that can be found in the table 'Tijdsintervallen'. This means that per day all the 96 intervals have to be represented in the result. If no results are found for an interval in the table 'Tussen_Tickets', zero has to be filled in the columns DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold and Talk.The result has to have the following columns:Date (datetime), Interval from table Tijdsintervallen, and the columns Event_Channel, DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold and Talk from the table Tussen_tickets.I hope I'm clear and complete enough with my question and explanation.[/quote]Reckon so.[quote]Who is willing to help me out with this query?[/quote]There will be no shortage of volunteers...[quote]In attachment you will find a text file to create the 2 test tables and fill them with data.[/quote]...once the ddl and dml appear ;-)It's a fairly common requirement. Thu, 28 Feb 2013 08:09:45 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxHello and welcome to SSC,If you could knock up some sample data and DDL scripts, then your expected results based on the sample data provided that would be extremely useful in allowing people to help you.Have a [url=http://www.sqlservercentral.com/articles/Best+Practices/61537/]read through this link --> http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url], if you're unsure how best to lay this information out. Remember that if your sample data and DDL script is readily consumable, then the volunteers for this forum are much more likely to take time out from their work to give you a hand.Thanks.Thu, 28 Feb 2013 08:03:22 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1425049-392-1.aspxHi everyone,I have been searching for a few days now to try to solve a problem, but without success. What I did find on the net wasn't what I want.We have a large DB with a lot of tables. From those tables I have to use 2 tables in my query.The query has to group all the records from the table 'Tussen_Tickets' per time interval of 15 minutes based on the column Ticket_Closed_DateTime and group it also per Event_Channel which is a column in the table. The intervals can be found in the second table with the name 'Tijdsintervallen'. The result has also to represent all the intervals that can be found in the table 'Tijdsintervallen'. This means that per day all the 96 intervals have to be represented in the result. If no results are found for an interval in the table 'Tussen_Tickets', zero has to be filled in the columns DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold and Talk.The result has to have the following columns:Date (datetime), Interval from table Tijdsintervallen, and the columns Event_Channel, DVBIntern, DVBExtern, Tijdsduur, WrapUp, OnHold and Talk from the table Tussen_tickets.I hope I'm clear and complete enough with my question and explanation.Who is willing to help me out with this query?In attachment you will find a text file to create the 2 test tables and fill them with data.Thank you very much for your help and assistance.Greetz,Geert Thu, 28 Feb 2013 07:58:32 GMTgeert.de.vylder