How can I query dates to when a specific column value changes for a given id? The table below shows the dates when a job is ran to identify the status for the oppid. I want to track when the oppid has status = status_1 to show the date earliest date when the status is set and the date when the status is <> status_1.
| oppid | date | status |
+--------+ ---------+-----------+
| 1000 | 2020-07-01| status_1 |
| 1000 | 2020-07-02| status_1 |
| 1000 | 2020-07-03| status_1 |
| 1000 | 2020-07-04| status_2 |
| 1000 | 2020-07-07| status_2 |
| 1000 | 2020-07-15| status_1 |
| 1000 | 2020-07-16| status_1 |
| 1001 | 2020-07-10| status_1 |
| 1001 | 2020-07-11| status_1 |
| 1000 | 2020-08-01| status_2 |
Desired outcome to look something like the below:
| oppid | status | status_set | status_changed |
+--------+ ---------+-------------+----------------+
| 1000 | status_1 | 2020-07-01 | 2020-07-04 |
| 1000 | status_1 | 2020-07-15 | 2020-08-01 |
| 1001 | status_1 | 2020-07-10 | |
You can use window functions:
select oppid, status, date as status_set, status_changed
from (select t.*,
lag(status) over (partition by oppid order by date) as prev_status,
min(case when status <> 'status_1' then date end) over (partition by oppid order by date desc) as status_changed
from t
) t
where (prev_status is null or prev_status <> status) and
status = 'status_1';
The previous status is used for filtering to find each time that the status becomes 'status_1'. The min() is used to get the next date when the status is not 'status_1'.