CODEWITHSUNDEEP
sql-servert-sql
Cole Mietzner
Cole Mietzner

Reputation: 313

SQL Server query to show tasks running concurrently

I have a table that records tasks run withing our application. It has 3 relevent columns; TaskName, StartDateTime, and EndDateTime.

I need to write a query that will show what tasks are running being run at the same time.

For example

       **TaskName  |      StartDateTime      |  EndDateTime**
          Task1      2014-08-18 11:16:29.000    2014-08-18 11:19:29.000
          Task2      2014-08-18 11:17:29.000    2014-08-18 11:17:34.000        
          Task3      2014-08-18 11:18:29.000    2014-08-18 11:18:52.000
          Task4      2014-08-18 11:18:53.000    2014-08-18 11:21:12.000

Per my example, while Task1 is running Task2, and Task3 start and finish before Task 1 has finished. Task4 starts before task1 finishes but finishes after Task1 completes.

I want to be able to see the concurrent tasks to better analyze performance issues.

I don't even know where to start, if there is a example somewhere I can look at that would probably be enough for me to figure it out.

Upvotes: 0

Views: 134

Answers (1)

AHiggins
AHiggins

Reputation: 7219

Would something like this work for you? I left some commented-out portions in case you want to retrieve the data from both sides. 

DECLARE @StackOverflow TABLE (TaskName VARCHAR(5), StartDateTime DATETIME, EndDateTime DATETIME)
INSERT INTO @StackOverflow (TaskName, StartDateTime, EndDateTime)


SELECT 'Task1','2014-08-18 11:16:29.000','2014-08-18 11:19:29.000' UNION 
SELECT 'Task2','2014-08-18 11:17:29.000','2014-08-18 11:17:34.000' UNION         
SELECT 'Task3','2014-08-18 11:18:29.000','2014-08-18 11:18:52.000' UNION 
SELECT 'Task4','2014-08-18 11:18:53.000','2014-08-18 11:21:12.000' 

SELECT * FROM @StackOverflow

SELECT a.TaskName + ' begins while ' + b.TaskName + ' is running' Result--, a.*, b.* 
FROM 
    @StackOverflow a
     INNER JOIN 
    @StackOverflow b ON 
        a.TaskName <> b.TaskName AND 
        a.StartDateTime BETWEEN b.StartDateTime AND b.EndDateTime 
--UNION 
--SELECT b.TaskName + ' begins while ' + a.TaskName + ' is running'--, a.*, b.*
--FROM 
--  @StackOverflow a
--   INNER JOIN 
--  @StackOverflow b ON 
--      a.TaskName <> b.TaskName AND 
--      a.EndDateTime BETWEEN b.StartDateTime AND b.EndDateTime 
ORDER BY Result

Result

Task2 begins while Task1 is running

Task3 begins while Task1 is running

Task4 begins while Task1 is running

Upvotes: 1

Related Questions