First, let me say-my background is in AACC (and SQL) and not Elite/CMS. So the following lengthy reply may be unhelpful and I've prioritized the order of this response from what I think is the most important piece of information to the least, that way if you get bored at any point and stop reading, as long as you've read top down, you probably won't be missing anything important.
Second, in pure SQL (more on this in #4 below), grouping by time and not including the DATE in your SELECT would limit the output to grouping all times in a date range to a single day.
For example, if you have the dates 7/5 – 7/12, times 8A-5P-but you group by the hour, an SQL report would return 8A-5P as groups (each day in the date range would be summarized. Like saying-Tell me my total calls for the week between 8-9, 9-10, 10-11, etc.)
Your solution might be as simple as adding the date to your SELECT and GROUP BY clauses. (SELECT ROW_DATE, STARTTIME ... GROUP BY 1, 2, 4 ...)
Also, you probably want to ORDER BY ROW_DATE, STARTTIME. (Although the data may often be returned in sequence because of how it is written to the database, it's a good practice in SQL to get in the habit of being specific.)
This will, necessarily, require you to modify your totals query. (So it has the same number of columns as your GROUP BY query.)
Third, WHERE and HAVING are similar in SQL. WHERE evaluates per-row, whereas HAVING evaluates per-group. Changing WHERE will change what content to include in your groups, changing HAVING will change which groups to return. An example would be if you wanted to do an agent report, you could group by first(agent), avg(acdtime) and return groups having acdtime>12 minutes. This would give you a list of with high average talk time (assuming 12 minutes talk time is high in your environment.) If you added a WHERE ACDCALLS > 0 clause, it will omit any rows where the agent took no calls (as the average will be reduced when including idle intervals. This will tend to bump up the average. Maths.)
Fourth, I'm not entirely positive there isn't an interpreter at work before the SQL query is processed. I've seen this kind of syntax before in a report writing system but not in raw SQL. In SQL you would use ROW_DATE BETWEEN STARTTIME AND ?ENDTIME? instead of ROW_DATE = [Dates:]. An acceptable SQL alternative would be (ROW_DATE >= STARTIME AND ROW_DATE < ?ENDTIME?)
Having said that, I did some google searches and found several community support threads where ROW_DATE = [Dates:] was reported to be the correct syntax. (Thus, my arriving at the conclusion that this is possibly working with a pre-SQL interpreter.)
Likewise, instead of VECTOR = [Vector:], in SQL you would say VECTOR IN {variable,[list,...]}
If VECTOR IN [101,102, 201, 301], return the row. This works for a list of discrete entities, not so much date and time.
Thank you
Christopher Williams
Architect, Telephony Engineering
CONFIDENTIALITY NOTICE: This e-mail, including attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information or otherwise be protected by law. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender and destroy all copies and the original message.