sql - Query Records and Group by a block of time

Question

I have an application that may be run several times a day. Each run results in data that is written to a table to report on events that occurred. The main report table looks something like this:

Id    SourceId    SourceType    DateCreated
5048  433         FILE          5/17/2011 9:14:12 AM
5049  346         FILE          5/17/2011 9:14:22 AM
5050  444         FILE          5/17/2011 9:14:51 AM
5051  279         FILE          5/17/2011 9:15:02 AM
5052  433         FILE          5/17/2011 12:34:12 AM
5053  346         FILE          5/17/2011 12:34:22 AM
5054  444         FILE          5/17/2011 12:34:51 AM
5055  279         FILE          5/17/2011 12:35:02 AM

I can tell that there were two runs, but I would like a way to be able to query for a date range, the number of times the process was run. I would like to have a query that results in the time the process started and the number of files in the group. This query sort of gets me what I want in terms of I can see what day and hour and how many files were run, but not exactly how I would like. And it would not accomodate runs that ran from 8:58 to 9:04 for example. It also would group runs that started at 9:02 and 9:15 for example.

Select dateadd(day,0,datediff(day,0,DateCreated)) as [Date], datepart(hour, DateCreated) as [Hour], Count(*) [File Count]
From   MyReportTable
Where DateCreated between '5/4/2011' and '5/18/2011'
    and SourceType = 'File'
Group By dateadd(day,0,datediff(day,0,DateCreated)), datepart(hour, DateCreated)
Order By dateadd(day,0,datediff(day,0,DateCreated)), datepart(hour, DateCreated)

I understand that any runs that are close together will likely get grouped together, and I'm fine with that. I only expect to get a rough grouping.

Thanks!

Answer 1

Take it a few steps farther:

SELECT
    Count(Id), 
    DATEPART(year, DateCreated) As yr, 
    DATEPART(month, DateCreated) As mth, 
    DATEPART(day, DateCreated) As day, 
    DATEPART(Hour, DateCreated) as hr, 
    DATEPART(minute, DateCreated) as mnt
FROM 
    MyReportTable
WHERE DateCreated between '5/4/2011' and '5/18/2011'
    and SourceType = 'File'
GROUP BY 
    DATEPART(year, DateCreated), 
    DATEPART(month, DateCreated), 
    DATEPART(day, DateCreated), 
    DATEPART(Hour, DateCreated),
    DATEPART(minute, DateCreated)
ORDER BY 
    DATEPART(year, DateCreated),
    DATEPART(month, DateCreated), 
    DATEPART(day, DateCreated), 
    DATEPART(Hour, DateCreated),
    DATEPART(minute, DateCreated)

Edit

To get to a 15 minute resolution, change the last column to

(DATEPART(minute, DateCreated)/15)

(add +1 to that in the select to get 1,2,3,4).