How do I filter down the latest Date from multiple columns

Question

I've been working with it and got it to work somewhat, but my WHERE filter has errors, I need only the max date that is Actual in each category.

    left JOIN ( SELECT Customer_ID
    , MAX(Civil_Status_Date)as Civil_Date
    , MAX(Power_Status_Date)as Power_Date
    , MAX(Construction_Complete_Date)as CC_Date
      FROM [Server].[dbo].[Projects] (NOLOCK)
      WHERE Civil_Status = 'Actual' and Power_Status = 'Actual' and Construction_Complete = 'Actual' and 
      GROUP BY Customer_ID
    ) Projects ON Report.Customer_ID=Projects.Customer_ID

Sample dataset below, How would I get the latest Date from multiple columns marked "Actual". In the table below I want it to return

Customer /// Last_Construction_Date
3956 /// 4/9/2023
4655 /// 7/1/2023

Any help appreciated! ty!

Answer 1

Here is a somewhat complex solution, but given your requirements this will work -

CREATE TABLE #sample
(
    customer INT
  , civil_status VARCHAR(32)
  , civil_status_date DATE
  , power_status VARCHAR(32)
  , power_status_date DATE
  , construction_complete VARCHAR(32)
  , construction_complete_date DATE
);

INSERT INTO #sample
VALUES
(3956, 'Pending', '10-05-2024', 'Pending', '10-05-2024', 'Pending', '11-05-2024')
, (3956, 'Actual', '04-09-2023', 'Actual', '04-09-2023', 'Pending', '05-19-2023')
, (3956, 'Cancelled', '07-02-2023', 'Cancelled', '07-02-2023', 'Cancelled', '07-02-2023')
, (4655, 'Pending', '05-16-2026', 'Pending', '05-16-2026', 'Pending', '07-16-2026')
, (4655, 'Actual', '12-15-2022', 'Pending', '07-15-2023', 'Pending', '07-15-2023')
, (4655, 'Actual', '06-04-2023', 'Actual', '06-10-2023', 'Actual', '07-01-2023');

SELECT customer
     , COALESCE(construction_complete, power_status, civil_status) AS Actual_date
FROM
(
    SELECT up2.customer
         , up2.status_date
         , up2.state
    FROM
    (
        SELECT *
        FROM #sample
        WHERE civil_status = 'Actual'
                OR power_status = 'Actual'
                OR construction_complete = 'Actual'
    ) AS data_root
        UNPIVOT
        (
            status_date
            FOR status IN (civil_status_date, power_status_date, construction_complete_date)
        )   AS up
        UNPIVOT
        (
            current_state
            FOR state IN (civil_status, power_status, construction_complete)
        )   AS up2
    WHERE status = state + '_date'
            AND up2.current_state = 'Actual'
) filtered
PIVOT
(
    MAX(status_date)
    FOR state IN (civil_status, power_status, construction_complete)
) AS p;

Produces this result -

customer	Actual_date
3956	2023-04-09
4655	2023-07-01

DB fiddle