I have a table with a column named "create_time" (datetime, null) and there's another column "ID".
What I am trying to do is get count(ID) with an interval of 60 minutes.
If CURRENT_TIMESTAMP is say 2015-08-05 17:01:29.657, then I want to start from 2015-08-05 17:00:00.000 and take count(ID) when "create_time" is between 2015-08-05 17:00:00.000 and 2015-08-05 16:00:00.000.
I could come up with a query for this:
select State, count(ID) as Calls_per_hour from CTIRPTS.dbo.cti_reporting WHERE Create_time BETWEEN DATEADD(hour,-1,DATEADD(hour,DATEDIFF(hour,0,GETDATE()),0)) AND DATEADD(hour,DATEDIFF(hour,0,GETDATE()),0) group by State;
Now I want to do this for each hour (going in past time) until 2015-08-05 00:00:00.000 and calculate
- average of count(ID) (sum of all count(ID) until 2015-08-05 00:00:00.000 divided by number of times we took count(ID))
- minimum of count(ID) and maximum of count(ID)
I will have to do this for present day to get avg, min and max for the day and might have to find avg, min and max for last week as well.
Can you please suggest me how I should approach to get this done. After getting the above query, I am simply unable to think of a way to get this done and some direction will be much appreciated.
Thanks in advance!