April 24, 2014 at 11:21 am
Hi all,
Wonder if I am missing something obvious, but I can't seem to get it.
I need to come up with a query that returns start_time, end_time, count for every 5 min interval.
Here is the sample data:
create table A (process_date datetime);
insert into A (process_date)
values
('2014-04-24 00:00:00.000'),
('2014-04-24 00:01:05.000'),
('2014-04-24 00:01:07.000'),
('2014-04-24 00:02:09.000'),
('2014-04-24 00:02:10.000'),
('2014-04-24 00:03:11.000'),
('2014-04-24 00:03:13.000'),
('2014-04-24 00:04:14.000'),
('2014-04-24 00:04:14.000'),
('2014-04-24 00:05:14.000'),
('2014-04-24 00:05:14.000'),
('2014-04-24 00:06:15.000'),
('2014-04-24 00:06:15.000'),
('2014-04-24 00:07:16.000'),
('2014-04-24 00:08:16.000'),
('2014-04-24 00:08:16.000'),
('2014-04-24 00:09:16.000'),
('2014-04-24 00:09:16.000'),
('2014-04-24 00:09:16.000'),
('2014-04-24 00:10:16.000'),
('2014-04-24 00:10:16.000');
select * from A;
So, in this case I'd like to see:
start_date, end_date, count(*)
'2014-04-24 00:00:00.000', '2014-04-24 00:05:14.000', 11
'2014-04-24 00:05:14.000', '2014-04-24 00:10:16.000', 10
Thanks,
April 24, 2014 at 11:39 am
Why are the intervals in the desired output > 5 minutes?
April 24, 2014 at 12:24 pm
I agree your desired output doesn't match your description because you have intervals outside of the 5 minutes.
Something like this get you started?
select MIN(process_date) as StartDate, MAX(process_date) as EndDate, COUNT(*)
from A
group by DATEPART(minute, process_date) / 5
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2014 at 12:18 pm
Thanks for reply. Your query helped a lot.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply