Summarize Data with SQL statement

Question

I have CMS table that contain CMS_STARTTIME and CMS_STOPTIME (attachment).

I want to create report to summarize exist data by using Date, Time and Year as Parameter (attachment).

I don't know how to create sql statement to meet this requirement. I'm using MySQL and MS-SQL. Does one can help?
Thank you very much.

Answer 1

Here's a solution for SQL Server

SELECT 
 DATEPART(hh,CMS_StartTime)  AS HourOfDay, 
 COUNT(*)              AS NumMsgs, 
 SUM(DATEDIFF(mi,CMS_StartTime,CMS_STOPTIME)) AS TotalTimeInMinutes,
 AVG(DATEDIFF(mi,CMS_StartTime,CMS_STOPTIME)) AS AvgTimeBetweenInMinutes
FROM 
      MyTable   
WHERE CMS_StartTime
      BETWEEN 'may 1 2010' AND 'may 2 2010'
GROUP BY
      DATEPART(hh,CMS_StartTime)

Answer 2

To make this work, you will need a Numbers or Tally table which contains a sequential list of integers starting at zero. In my example, my Numbers table looks like:

Create Table Numbers ( Value int not null primary key )

Using that and doing this in SQL Server, you can do something like:

Select N.Value As [Hour]
    , Count(T.StartDate) As CallCount
    , Sum(DateDiff(mi, T.StartDate, T.EndDate)) As [TotalTime(mi)]
    , Avg(DateDiff(mi, T.StartDate, T.EndDate)) As [AvgTime(mi)]
From Numbers As N
    Left Join Table As T
        On DatePart(hh, T.StartDate) = N.Value
Where N.Value Between 0 And 23  
Group By N.Value

Since you did not specify, I assumed that a call counts in a given hour if it starts in that hour. An argument could made that a call counts in an hour if it starts or ends in a given hour which would create double counting but would tell you how many calls were in progress during a given hour of the day. To do that, the query above would need to be changed to add the following to the On clause Or DatePart(hh, T.EndDate) = N.Value.

I believe the MySql equivalent would be:

Select N.Value As [Hour]
    , Count(T.StartDate) As CallCount
    , Sum( TimestampDiff(MINUTE, T.StartDate, T.EndDate) ) As [TotalTime(mi)]
    , Avg( TimestampDiff(MINUTE, T.StartDate, T.EndDate) ) As [AvgTime(mi)]
From Numbers As N
    Left Join Table As T
        On Hour(T.StartDate) = N.Value
Where N.Value >= 0 And N.Value <= 23    
Group By N.Value