Databases
StackOverflow2013
Postsdbo
POSQL - Merging data from rows with overlapping dates
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL - Merging data from rows with overlapping dates
    primarykey
    data
    text
    <p>I have a table (JobsLogged) with the following fields and some sample data:</p> <pre><code>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 </code></pre> <p>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.</p> <p>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:</p> <pre><code>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 </code></pre> <p>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.</p> <p>I have searched the site and <a href="https://stackoverflow.com/questions/6174451/merge-date-rows-if-episodes-are-in-direct-succession-or-overlapping">this answer</a> 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.</p> <p>I have set up a <a href="http://sqlfiddle.com/#!3/2a654/2" rel="nofollow noreferrer">SQL Fiddle</a> 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.</p> <pre><code>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 </code></pre> <p>Thank you for your time in reading this. Any help would be greatly appreciated.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USPTD
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL - Merging data from rows with overlapping dates
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWouldn't using the min logdate and max closedate, grouped by profile and jobid work?
      singulars
      1. POSQL - Merging data from rows with overlapping dates
      1. UST I
    2. COIn 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?
      singulars
      1. POSQL - Merging data from rows with overlapping dates
      1. USPTD
    3. COEssentially 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.
      singulars
      1. POSQL - Merging data from rows with overlapping dates
      1. USPTD
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload