Creating an Hour field in an SQL Query CUIC v10.0

[sarbarnes]

I am trying to create a report that shows the calls offered etc by Hour, instead of by half hour or 15 minutes which comes with the usual DateTime (interval) query.

I am using DATEPART (Hour, DateTime) As Hour  and whilst this is giving me the hour number in the new column, I an showing 2 rows per hour as per the half hour interval.

I have included it in the Group By and this makes no difference, Whilst I can group them by hour and that would in effect solve the issue, I then loos the Date Field!

Ideally what I would like to see in 1 column is

Call Type 1    4/2/15 10:00 AM

                      4/2/15 11:00 AM

                      4/2/25 12:00 AM

etc.....

Instead what I have is

Call Type 1    4/2/15 10:00 AM         10

                      4/2/15 10:30 AM         10

                      4/2/25 11:00 AM         11

                      4/2/25 11:30AM          11

etc...

If there is anyone out there that can help I would be most grateful

Thanks Sarah

[Aaron Harrison]

Hi Sarah

Take a look at the sp_csq_activity_interval. I find it easiest to follow Cisco's style of development and reuse some of their procs.

That proc can be easily edited for any time interval by just changing the param on the SP, so you can use that as a template.

Aaron

[sarbarnes]

Hi Aaron, thanks for your reply, I forgot about UCCX reports, until I was training on it today, however I no longer have access to a UCCX db and therefore cannot get access to the SP, as you are right that is always the best way to go.

I find it strange that some of the reports in UCCX aren't available as standard in UCCE as there are a few which would be very useful.

Thanks again.

[Jameson Gagnepain]

Sarah,

You will need to make sure you remove "DateTime" from your GROUP BY clause, and from the SELECT statement.

Instead of using DATEPART, I would use something like:

DateTimeHour = DATEADD(h,DATEDIFF(h,0,DateTime),0)

The above will give you a full DateTime value, rounded down to the nearest hour. Don't forget to include it in the GROUP BY (and probably SORT BY).

You can add "DateTime" back as Filter Field in CUIC after you do the initial Create Fields step (look on the Fields page). Filtering on DateTime instead of DATEADD(h,DATEDIFF(h,0,DateTime),0) should make for a more efficient SQL query.

-Jameson

[sarbarnes]

Jameson, I was very near, I did exactly what you have written using DATEADD, however what I was seeing in the report was very strange, so I thought I had it wrong, as in it was throwing up a date of 1962 and a constant time of 12:00am!

I have tried it in the Lab system and it worked, so I will try it again and see how I get on.

thanks again