SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Grouping records by time interval


Grouping records by time interval

Author
Message
geert.de.vylder
geert.de.vylder
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 141
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.

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
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9546 Visits: 8492
Hello 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 read through this link --> http://www.sqlservercentral.com/articles/Best+Practices/61537/, 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.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43675 Visits: 20015
geert.de.vylder (2/28/2013)
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.

Reckon so.


Who is willing to help me out with this query?

There will be no shortage of volunteers...


In attachment you will find a text file to create the 2 test tables and fill them with data.

...once the ddl and dml appear ;-)

It's a fairly common requirement.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
geert.de.vylder
geert.de.vylder
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 141
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
Attachments
TestTables.txt (23 views, 19.00 KB)
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9546 Visits: 8492
geert.de.vylder (2/28/2013)
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


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: -
SELECT d.*
FROM #Tussen_Tickets
OUTER APPLY (SELECT Interval AS StartInterval
FROM #Tijdsintervallen
WHERE CAST(Ticket_Accepted_DateTime AS TIME) >= beginTijdsinterval AND CAST(Ticket_Accepted_DateTime AS TIME) <= eindTijdsinterval
) b
OUTER APPLY (SELECT Interval AS EndInterval
FROM #Tijdsintervallen
WHERE CAST(Ticket_Closed_DateTime AS TIME) >= beginTijdsinterval AND CAST(Ticket_Closed_DateTime AS TIME) <= eindTijdsinterval
) c
CROSS 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;



Which produces: -
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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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.000
2013-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



Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
geert.de.vylder
geert.de.vylder
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 141
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
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9546 Visits: 8492
geert.de.vylder (3/1/2013)
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


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.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43675 Visits: 20015
I reckon this does it:

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
) 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.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
) op
ORDER BY mx.Ticket_Closed_Date, mx.Interval, op.Event_Channel



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
geert.de.vylder
geert.de.vylder
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 141
Hi 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,

Geert
Attachments
ResultTable.txt (18 views, 7.00 KB)
geert.de.vylder
geert.de.vylder
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 141
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search