Selecting a group of dates in SQL Server

Question

I have a history table that captures updates to a certain object and, in addition to other information, captures the time this update happened. What I would like to do is SELECT the MIN(LogDate) corresponding to a certain ActionTaken column.

More specifically, the history table may have other (more recent) rows where ActionTaken = 1, but I want to capture the date ActionTaken became 1.

Example:

SELECT  MIN(LogDate) AS FirstActionDate
FROM    HistoryTable
WHERE   ID = 123
    AND FirstActionTaken = 1

SELECT  MIN(LogDate) AS SecondActionDate
FROM    HistoryTable
WHERE   ID = 123
    AND SecondActionTaken = 1

SELECT  MIN(LogDate) AS ThirdActionDate
FROM    HistoryTable
WHERE   ID = 123
    AND ThirdActionTaken = 1

This works well, and I receive the proper dates without issue. Where I'm running into trouble is then going to select the MAX(LogDate) from this group:

SELECT  MAX(LogDate) AS LastActionDate
FROM    HistoryTable
WHERE   ID = 123
    AND LogDate IN 
    (
            (   SELECT  MIN(LogDate) AS FirstActionDate
                FROM    HistoryTable
                WHERE   ID = 123
                    AND FirstActionTaken = 1    ),

            (   SELECT  MIN(LogDate) AS SecondActionDate
                FROM    HistoryTable
                WHERE   ID = 123
                    AND SecondActionTaken = 1   ),

            (   SELECT  MIN(LogDate) AS ThirdActionDate
                FROM    HistoryTable
                WHERE   ID = 123
                    AND ThirdActionTaken = 1    )
    )

This also works, but I hate doing it this way. I could save out the previous statements into variables and just SELECT MAX() from those; it would certainly be more readable, but what would the JOIN syntax look like for this query?

Is there a way to combine the first three SELECT statements into one that returns all three dates and isn't an unreadable mess?

How can I grab the most recent LogDate (as a separate column) from this result set and without the (seemingly unnecessary) repeating SELECT statements?

EDIT:

Here are a few links I've found in relation to the answers that have been given so far:

• Data Normalization
• Using OUTER/CROSS APPLY
• UNPIVOT (and others)

Hopefully these will help with others looking for solutions to similar problems!

Answer 1

This would be easier with a normalized data structure. Here is one method that uses conditional aggregation to calculate the three minimum dates. Then it takes the maximum of those values:

SELECT v.dt
FROM (SELECT MIN(CASE WHEN FirstActionTaken = 1 THEN LogDate END) AS d1,
             MIN(CASE WHEN SecondActionTaken = 1 THEN LogDate END) AS d2,
             MIN(CASE WHEN ThirdActionTaken = 1 THEN LogDate END) AS d3      
     FROM HistoryTable
     WHERE ID = 123
    ) ht OUTER APPLY
    (SELECT MAX(dt) as dt
     FROM (VALUES (d1), (d2), (d3) ) v(dt)
    ) v;