Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Grouping records by time interval Expand / Collapse
Author
Message
Posted Thursday, February 28, 2013 7:58 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, 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
Post #1425049
Posted Thursday, February 28, 2013 8:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:47 AM
Points: 2,379, Visits: 7,565
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1425054
Posted Thursday, February 28, 2013 8:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 6,750, Visits: 13,898
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
Post #1425061
Posted Thursday, February 28, 2013 9:27 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, 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


  Post Attachments 
TestTables.txt (13 views, 19.38 KB)
Post #1425112
Posted Thursday, February 28, 2013 9:49 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:47 AM
Points: 2,379, Visits: 7,565
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



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1425142
Posted Friday, March 1, 2013 1:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, 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
Post #1425394
Posted Friday, March 1, 2013 2:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:47 AM
Points: 2,379, Visits: 7,565
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1425398
Posted Friday, March 1, 2013 3:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:44 AM
Points: 6,750, Visits: 13,898
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
Post #1425416
Posted Friday, March 1, 2013 3:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, 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


  Post Attachments 
ResultTable.txt (8 views, 7.84 KB)
Post #1425418
Posted Friday, March 1, 2013 6:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:47 AM
Points: 24, 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
Post #1425471
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse