0

As in below list, consider a table of tasks with the time when they started to the time when they ended.

I need to find the optimal method/SQL Query to find the top 5 times when maximum number of tasks where running may be with 1 second accuracy or by range

TaskID |    StartDateTime           |   EndDateTime
1      |    2016-04-23 10:29:48.643 |   2016-04-23 10:29:53.077
2      |    2016-04-23 10:32:58.797 |   2016-04-23 10:33:01.643
3      |    2016-04-23 10:36:09.227 |   2016-04-23 10:36:12.270
4      |    2016-04-23 10:46:58.093 |   2016-04-23 10:47:01.863
5      |    2016-04-23 10:53:08.107 |   2016-04-23 10:53:12.683

So the output can be like

DateTime            | NoOfTasksRunning
2016-04-23 10:29:48 | 5
2016-04-23 12:29:48 | 4
2016-04-23 10:59:48 | 4
2016-04-23 01:29:48 | 1

Or like

Duration                                   | NoOfTasksRunning
2016-04-23 10:29:48 to 2016-04-23 10:29:58 | 5
2016-04-23 10:39:48 to 2016-04-23 10:59:00 | 4
2016-04-23 01:29:48 to 2016-04-23 02:29:58 | 1

The most obvious answer is to find largest and smallest datetime and loop through each and every second and get counts but that is not the most efficient way to get to it so please dont include that as your answer.(hoping its not the only way)

Note: there is no correlation in data between different tables above. they are just example to give idea of the output needed or in general the output desired.

No SQL version restrictions, just that it has to be MS SQL and no MySQL or oracle queries.

user3754372
  • 177
  • 16
  • Is this what you are looking for? Its specific to dates but should work the same for datetime. http://stackoverflow.com/questions/4490553/detect-overlapping-date-ranges-from-the-same-table – Joe C May 05 '16 at 23:01
  • What if all of your tasks are running at once for a 10-second interval? Should your output list the first of those 10 seconds, or all 10 of them? – APH May 05 '16 at 23:34
  • @JoeC Its not the same question, sorry – user3754372 May 06 '16 at 01:02
  • @APH First or all of them. depending on which format of result you are trying to produce from the 2 possible results i gave. For my first result, you would show all, but for 2nd, you would only show 1 row with duration of that 10 seconds. – user3754372 May 06 '16 at 01:05
  • T-SQL isn't the ideal language for all problems. You would get a better solution using C#. You could do this using SQL Server CLR Integration or a mid-tier layer. See Books Online > Introduction to SQL Server CLR Integration: https://msdn.microsoft.com/en-us/library/ms254498(v=vs.110).aspx – RichardCL May 06 '16 at 08:04

1 Answers1

0

So, i solved it by finding all start and end dates, group them in single column, order them ascending and keep only unique.

This gave me list of key date times between which, the no of jobs would not have changed.

Then, i used the list of key date time table to define start and end date of timespan and found how many jobs were running during that timespan. This gave me the number of jobs that were running at different time spans.

ordering it by NoOfJobsRunning in descending gave me the 2nd version of output in my question.

user3754372
  • 177
  • 16