Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2013

    Unanswered: sql query each hour of a day and results in 1 column

    I'm new to informix and I'm using it because we use cisco uccx and we need to run detailed reports for our call center to get better statistics. My superiors want a report that will get me the number of calls each hour so that over time we can see what times of days and days of weeks are the busiest. The query I have now:

    select COUNT(*)
    from ContactCallDetail
    where contactType=1 and startDateTime > '2013-07-17 15:00:00' and startDateTime < '2013-07-17 16:00:00';

    works good for one given hour. I would like to be able to do this for about a 11 hours period but not sure how to go about the query. I'm thinking the count would be in 1 column and each row would be an hour of the day and how the count of calls. does anyone have any suggestions on how I can go about this? I really appreciate any help.

    Thank You

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    I'd try something like:
    SELECT TO_CHAR(startDateTime, '%d %R'), Count(*)
       FROM ContactCallDetail
       GROUP BY TO_CHAR(startDateTime, '%d %R')
       ORDER BY TO_CHAR(startDateTime, '%d %R')
    You'll probably want to adjust this in many ways, but it might get you started.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2013
    Thanks a lot Pat I really appreciate your help. I'm not there yet but this has definitely put me on the right track.


  4. #4
    Join Date
    Sep 2011
    Pont l'Abbé, Brittany, France
    Provided Answers: 1
    Although this might be a big bang in your project, you should seriously take a look at the TimeSeries Datablade if time series are a central point in your application.

    As an introduction to TimeSeries, take a look here IBM Informix TimeSeries data management
    and also take a look here

    TimeSeries is included for free in IBM Informix if you have version greater or equal to 11.70 xC1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts