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!
See Question&Answers more detail:os