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