Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql help, please Expand / Collapse
Author
Message
Posted Thursday, April 24, 2014 11:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:42 AM
Points: 102, Visits: 346
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,
Post #1564796
Posted Thursday, April 24, 2014 11:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
Why are the intervals in the desired output > 5 minutes?
Post #1564802
Posted Thursday, April 24, 2014 12:24 PM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:54 AM
Points: 13,468, Visits: 12,323
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1564819
Posted Friday, April 25, 2014 12:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:42 AM
Points: 102, Visits: 346
Thanks for reply. Your query helped a lot.
Post #1565219
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse