How to turn a column of timestamps into two columns depending on separate column without splitting r-CodePudding

I have a table that has a column for timestamps and another column that has statuses. I want to grab the timestamp when the status is checked-in as well as the timestamp when it is completed into one row. When I try to use a case statement I end up with it split into two rows. I am wanting it to return one row with the values in each timestamp column rather than two rows one for each with the null in the other.

CASE WHEN aud.STATUS_DESCRIPTION = 'CHECKED_IN' THEN aud.STATUS_DATETIME
END AS "Check-In Time",
CASE WHEN aud.STATUS_DESCRIPTION = 'COMPLETED' THEN aud.STATUS_DATETIME 
END AS "Completed Time",

Table with statuses and timestamps
What my case statement is returning

Thank you for any help

CodePudding user response：

This is happening because your data is across many rows.

You ether need to do some form of aggregation, so GROUP BY and then using an aggregate function like MIN/MAX

OR You need to classify the data you want, and then use a PIVOT to do the aggregation for you.

The first might look like:

SELECT 
    some_column_a,
    some_column_b,
    MAX(IFF( aud.status_description = 'CHECKED_IN', aud.status_datetime, null)) as check_in_time
    MAX(IFF( aud.status_description = 'COMPLETED', aud.status_datetime, null)) as complete_time
FROM table  
GROUP BY some_column_a, some_column_b
ORDER BY some_column_a, some_column_b;

CodePudding user response：

I'd start with self-join.

SELECT 
    chcecked.STATUS_DATETIME as CHECKED_IN_TIME,
    completed.STATUS_DATETIME as COMPLETED_TIME
FROM
    yourtable as checked
JOIN
    yourtable as completed
ON ....