SQL query to return most recent record update based on multiple date columns (tricky?)

Question

I have a table that tracks that date of various stages of completion for a set of equipment. I need to identify the most recently updated piece of equipment (i.e., the equipment with the most recent date stamp) regardless of which stage was updated and return the equipment id, the update date and the stage. Easy, right? Well, it's a bit trickier than it sounds. Here's the catch: each stage is represented as a different field in the table and redesigning the table structure isn't an option (a transactional table would make this a no brainer.) Here's what the table structure looks like.

equip_id (text)
stage_1_process (date)
stage_2_process (date)
stage_3_process (date)
stage_4_process (date)
stage_5_process (date)

If the field contains a date then the stage is considered complete; if the field is empty then the stage is considered unfinished.

A union query with a Max() function can get the most recent date:

SELECT Max(dt) AS latest 
FROM (
    SELECT Max(stage_1_process) AS dt, 'Stage 1' as stage
    FROM equip_status
    UNION
    SELECT Max(stage_2_process) AS dt, 'Stage 2' as stage
    FROM equip_status
    UNION
    SELECT Max(stage_3_process) AS dt, 'Stage 3' as stage
    FROM equip_status
    UNION
    SELECT Max(stage_4_process) AS dt, 'Stage 4' as stage
    FROM equip_status
    UNION
    SELECT Max(stage_5_process) AS dt, 'Stage 5' as stage
    FROM equip_status) AS U;

The 'stage' alias is added to capture which stage that latest date is associate with. However, I don't know how to return it in the top-level query nor how to capture the equip_id, as it isn't derivable from an aggregate function.

Here's an example of the result set I need to return:

equip_id | stage   | latest
--------------------------------------------
A12345   | Stage 3 | 4/21/2016 3:56:39 PM

Thanks for any help!

Answer 1

You can join using the query of your post as a derived table:

SELECT t1.equip_id, 
       t2.lastest,
       CASE t2.latest
          WHEN t1.stage_1_process THEN 'Stage 1'
          WHEN t1.stage_2_process THEN 'Stage 2'
          WHEN t1.stage_3_process THEN 'Stage 3'
          WHEN t1.stage_4_process THEN 'Stage 4'
          WHEN t1.stage_5_process THEN 'Stage 5'
       END AS stage
FROM equip_status AS t1
JOIN (
  SELECT Max(dt) AS latest 
  FROM (
    SELECT Max(stage_1_process) AS dt, 'Stage 1' as stage
    FROM equip_status
    UNION
    SELECT Max(stage_2_process) AS dt, 'Stage 2' as stage
    FROM equip_status
    UNION
    SELECT Max(stage_3_process) AS dt, 'Stage 3' as stage
    FROM equip_status
    UNION
    SELECT Max(stage_4_process) AS dt, 'Stage 4' as stage
    FROM equip_status
    UNION
    SELECT Max(stage_5_process) AS dt, 'Stage 5' as stage
    FROM equip_status) AS U
) AS t2 ON t2.latest IN (t1.stage_1_process, 
                         t1.stage_2_process,
                         t1.stage_3_process,
                         t1.stage_4_process,
                         t1.stage_5_process)

This looks rather ugly, but these are the consequences of not properly normalised table structures.

Answer 2

One way is to implement a function that returns the value of the greatest stage given the row's 5 stages:

From http://allenbrowne.com/func-09.html

Function MaxOfList(ParamArray varValues()) As Variant
  Dim i As Integer        'Loop controller.
  Dim varMax As Variant   'Largest value found so far.

  varMax = Null           'Initialize to null

  For i = LBound(varValues) To UBound(varValues)
      If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
          If varMax >= varValues(i) Then
              'do nothing
          Else
              varMax = varValues(i)
          End If
        End If
  Next

  MaxOfList = varMax 
End Function

Now you can use a case statement along with MaxOfList

select top 1 
    equip_id,
    latest,
    case when stage_1_process = latest then 'stage 1'
    when stage_2_process = latest then 'stage 2'
    when stage_3_process = latest then 'stage 3'
    when stage_4_process = latest then 'stage 4'
    when stage_5_process = latest then 'stage 5'
    else 'none' end
 from (
    select *, 
        MaxOfList(stage_1_process,stage_2_process,stage_3_process,
                stage_4_process,stage_5_process) as latest
    from equip_status
) t order by latest desc