Click here to Skip to main content
15,845,742 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Table Structure
CSS
TrackId StartTime   EndTime
1   2014-10-16 05:48:17 2014-10-16 05:52:25
2   2014-10-16 05:49:17 2014-10-16 05:52:25
1   2014-10-16 05:49:17 2014-10-16 05:52:25
1   2014-10-17 10:49:17 2014-10-16 10:51:25



Out put needed
CSS
Time    Count
2014-10-16 05:49:00 1
2014-10-16 05:50:00 3
2014-10-16 05:51:00 3
2014-10-16 05:52:00 3
2014-10-17 10:50:00 1
2014-10-17 10:51:00 1


Please help to build select query
Posted

1 solution

you can round the time to the nearest minute
SQL
declare @dt datetime
set @dt = '09-22-2007 15:07:38.850'
select dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0)


Output : 2007-09-22 15:08:00.000

Read this thread for more details
 
Share this answer
 
v2
Comments
Jithin Geroge 30-Oct-14 0:53am    
Please little be specific for my descried output. I need the interval between startdate and enddate and the count of that intervals.. Please help
Dilan Shaminda 30-Oct-14 1:04am    
get the difference between start and end dates after rounded them to the nearest minute.Then you can use count(*) to get the intervals and don't forget to group by time when you are counting them
Dilan Shaminda 30-Oct-14 1:06am    
check this thread as well

http://stackoverflow.com/questions/10087605/get-all-dates-with-a-week-interval-given-a-start-date-and-end-date

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900