February 28, 2013 at 7:58 am
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
February 28, 2013 at 8:03 am
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/%5B/url%5D, 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.
February 28, 2013 at 8:09 am
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.
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
February 28, 2013 at 9:27 am
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
February 28, 2013 at 9:49 am
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
March 1, 2013 at 1:46 am
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
March 1, 2013 at 2:21 am
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.
March 1, 2013 at 3:36 am
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
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
March 1, 2013 at 3:38 am
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
March 1, 2013 at 6:36 am
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
March 1, 2013 at 6:42 am
geert.de.vylder (3/1/2013)
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 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.
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
March 4, 2013 at 8:36 am
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
March 4, 2013 at 8:44 am
geert.de.vylder (3/4/2013)
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
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_DateTime
ON [dbo].[Tussen_Tickets] ([Ticket_Closed_DateTime])
INCLUDE ([Event_Channel],[Tijdsduur],[WrapUp],[OnHold],[DVBIntern],[DVBExtern],[Talk])
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
March 4, 2013 at 8:49 am
What version of SQL Server are you connecting to?
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
March 4, 2013 at 9:10 am
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
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply