2

I have a table (JobsLogged) with the following fields and some sample data:

JobID    Profile    LogDate               CloseDate
======   ========   ===================   ===================
1112     Network    2013-03-05 15:55:00   2013-03-05 16:25:00
1113     Server     2013-03-05 16:00:00   2013-03-06 08:25:00
1114     Server     2013-03-05 16:05:00   2013-03-06 08:30:00
1115     Network    2013-03-05 17:00:00   2013-03-06 09:30:00
1116     Software   2013-03-06 09:00:00   2013-03-07 14:30:00
1117     Network    2013-03-06 10:00:00   2013-03-06 12:00:00
1118     Network    2013-03-06 11:00:00   2013-03-06 12:30:00
1119     Network    2013-03-06 11:30:00   2013-03-06 12:00:00

I need to create a report that calculates the downtime of each of the Profiles in the table. I have created a function in SQL that takes the LogDate and CloseDate of each of the jobs logged and compares them against another table that stores the 'potential uptime' for each day of the year (bank holidays, weekends, etc. are not included in the downtime calculation). The function returns the total downtime in minutes and works fine.

The problem I am having is that if the Logdate and/or CloseDate of one call in a specific Profile falls between the LogDate and CloseDate of a previous call, the downtime calculated will be duplicated, for example:

LogDate                CloseDate              Downtime
====================   ====================   ==============
2013-03-06 10:00:00    2013-03-06 12:00:00    120 minutes
2013-03-06 11:00:00    2013-03-06 12:30:00    90 minutes
2013-03-06 11:30:00    2013-03-06 12:00:00    30 minutes
                                              --------------
                                              240 minutes

In actual fact the downtime would be 150 minutes, due to the overlapping times of the calls. I need to group the data by Profile (which is easy enough) and then merge the rows within each Profile group where there are records in which the dates overlap. The earliest LogDate of the overlapping calls would become the group's LogDate while the latest CloseDate out of the group would become the group's CloseDate.

I have searched the site and this answer is pretty close to what I am trying to achieve, but the LEAD function doesn't appear to have been implemented in SQL Server until the 2012 version, and I am fairly inexperienced and lack the knowledge to find a suitable workaround.

I have set up a SQL Fiddle that creates a stripped back version of what I am working with. The SELECT statement will be used in a proc that will be passed into SSRS 2008 to run the reports. Ultimately, I would like my data to be displayed in a similar format to below, but I'm at a loss on how to go about grouping the overlapping data to achieve this.

Profile    LogDate               CloseDate             Downtime    JobID
========   ===================   ===================   =========   ======
Network    2013-03-05 15:55:00   2013-03-05 16:25:00   30          1112
           -------------------   -------------------   ---------   ------
           2013-03-05 17:00:00   2013-03-06 09:30:00   150         1115
           -------------------   -------------------   ---------   ------
           2013-03-06 10:00:00   2013-03-06 12:30:00   150         1117
                                                                   1118
                                                                   1119
--------   -------------------   -------------------   ---------   ------
Server     2013-03-05 16:00:00   2013-03-06 08:30:00   150         1113
                                                                   1114
--------   -------------------   -------------------   ---------   ------
Software   2013-03-06 09:00:00   2013-03-07 14:30:00   930         1116

Thank you for your time in reading this. Any help would be greatly appreciated.

Community
  • 1
  • 1
PTD
  • 1,028
  • 1
  • 16
  • 23
  • Wouldn't using the min logdate and max closedate, grouped by profile and jobid work? – T I Apr 01 '13 at 16:34
  • In the data provided in the middle example it would, but not all the dates in the table overlap. How would it be able to ascertain which dates overlapped and which didn't? – PTD Apr 01 '13 at 16:51
  • Essentially I need something that will loop through the rows for each profile group, keep any dates that don't overlap as they are while grouping any that do overlap together. – PTD Apr 01 '13 at 16:59

1 Answers1

0

In case anyone is having trouble with this type of issue in future, I solved my problem by using a cursor to loop through the records and group together any that overlapped. The results of this were stored in a temporary table while the cursor ran, before running the SELECT statement against the temporary table. An iteration of what I did can be found on my modified SQL Fiddle.

Once I had imported this data into SSRS I used a sub-report to return the JobIDs relating to each group of records, thus achieving the result I demonstrated in my question.

I hope this can be of use to others who are having the same problems that I was having.

PTD
  • 1,028
  • 1
  • 16
  • 23