2019: Week 12 Solution

The full solution can be seen below and downloaded here.

The full workflow.

Converting date and time to DateTime

Within the Manual Capture Error List  (MCEL) we were given [Start Date] and [Start Time], but really we want [Start DateTime]. There were a number of ways to go about this, including converting to string and using DATEPARSE() and adding the dates together using DATEADD(). I think the simplest solution however was to use the MAKEDATETIME() function as below:

MAKEDATETIME([Start Date], [Start Time])

The MAKEDATETIME() function takes the date part of the first parameter and combines it with the time part of the second parameter. In our case that means it combining the date from [Start Date] and the time from [Start Time] together. No need to do any tricky parsing or conversions as it does it for us!

Filtering out overlapping errors

This was the main challenge of the week – it was tricky to even figure out if it was possible. In the end we’ve come up with a pretty straightforward solution using Right-Outer joins in order to filter out rows from the MCEL and then just unioning the remaining rows with the Automatic Error Log (AEL).

To break this down I’ll first cover the 3 situations we need to look for and filter out in our data:

1. Any row in MCEL where both:
• The MCEL [Start DateTime] is AFTER the AEL [Start Date/Time] AND
• The MCEL [Start DateTime] is BEFORE the AEL [End Date/Time].
2.  Any row in the MCEL where both:
• The MCEL [End DateTime] is AFTER the AEL [Start Date/Time] AND
• The MCEL [End DateTime] is BEFORE the AEL [End Date/Time].
3. Any row in the MCEL where both:
• The MCEL [Start DateTime] is BEFORE the AEL [Start Date/Time] AND
• The MCEL [End DateTime] is AFTER the AEL [End Date/Time].

In situation 1, the MCEL error starts whilst the AEL error is on-going.
In situation 2, the MCEL error starts before the AEL error but ends whilst the AEL error is on-going.
In situation 3, the AEL error starts and finishes whilst the MCEL error is on-going.

These 3 situations all result in an overlap and require removing. This can be achieved with three right (or left depending on the order of your inputs)-outer-joins.

1. Take a right-outer-join on:
• MCEL [Start DateTime] >= AEL [Start Date/Time] AND
• MCEL [Start DateTime] <= AEL [End Date/Time]
  
  This leaves only rows where situation 1 has not occurred as they are in the “inner” section of the join.
  

  

  Checking there is no [Start DateTime] overlap.

  
  
2. Take a right-outer-join of the results of the previous join on:
• MCEL [End DateTime] >= AEL [Start Date/Time] AND
• MCEL [End DateTime] <= AEL [End Date/Time].
  
  This now leaves only rows where situation 1 AND 2 haven't occurred as they are in the "inner" section of this join and the previous join.

  

  Checking there is no [End DateTime] overlap.

  
  
3. Take a right-outer-join of the results of the previous join on:
• MCEL [Start DateTime] <= AEL [Start Date/Time] AND
• MCEL [End DateTime] >= AEL [End Date/Time].
  
  This now leaves only rows where situations 1, 2, and 3 haven't occurred as they are all in the "inner" section of this join and the previous 2 joins.

  

  Checking there is no complete overlap.

  
  

After performing these three joins you should be left with just two rows of data from the MCEL. In the screenshots above you can see how these rows get whittled down. The remaining two rows have no overlap with the AEL data as required, so we can now simply union them together!

Calculating Downtime in Hours

In order to calculate the downtime in hours for each error, you might think you could just use the following function:

DATEDIFF(
    'hour',
        [Start Date / Time], 
        [End Date / Time]
    )

The problem with this is that in our desired output we have decimal places. The function above rounds all these values to whole hours. In order to get the difference in hours with decimal places we can first calculate the difference in seconds, and then divide this by 60^2 (as there are 60 seconds in a minute and 60 minutes in an hour.

ROUND(
DATEDIFF(
    'second',
        [Start Date / Time], 
        [End Date / Time]
    )/(60^2),
    1
)

In the function about I’ve then wrapped this all inside a ROUND() function in order to round all my answers to 1 decimal place.