Assign Updated Time when a specific column change in SQL

Question

I have this table with values:

DWKey	Original Id	PartnerProgramStatusDWKey	UpdatedTime	LastUpdatedDate
2614	3584	2	2023-11-10	2023-11-10
3731	3584	2	2023-12-20	2023-11-10
4436	3584	2	2024-01-02	2023-11-10
4454	3584	1	2024-01-02	2024-01-02
4888	3584	1	2024-01-09	2024-01-02
5343	3584	1	2024-01-15	2024-01-02
22600	3584	2	2024-08-16	2023-11-10
22909	3584	2	2024-08-21	2023-11-10
23264	3584	2	2024-08-27	2023-11-10

I am currently using FIRST_VALUE column for the LastUpdatedDate to get the updated date when the PartnerProgramStatusDWKey changed. However, as you can see, the date column has the old last updated date as the program changed from 1 back to 2. So what I would like to have is whenever there is a change in the Program Status no matter what value, the last updated date will be equal to the updated date. Here is the desired output:

DWKey	Original Id	PartnerProgramStatusDWKey	UpdatedTime	LastUpdatedDate
2614	3584	2	2023-11-10	2023-11-10
3731	3584	2	2023-12-20	2023-11-10
4436	3584	2	2024-01-02	2023-11-10
4454	3584	1	2024-01-02	2024-01-02
4888	3584	1	2024-01-09	2024-01-02
5343	3584	1	2024-01-15	2024-01-02
22600	3584	2	2024-08-16	2024-08-16
22909	3584	2	2024-08-21	2024-08-16
23264	3584	2	2024-08-27	2024-08-16

Answer 1

We could approach this as a gaps and islands problem, with the minimum UpdatedTime value per island being the desired value.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY [Original Id]
                                 ORDER BY UpdatedTime, DWKey) rn1,
              ROW_NUMBER() OVER (PARTITION BY [Original Id], PartnerProgramStatusDWKey
                                 ORDER BY UpdatedTime, DWKey) rn2
    FROM yourTable
)

SELECT DWKey, [Original Id], PartnerProgramStatusDWKey, UpdatedTime,
       MIN(UpdatedTime) OVER (PARTITION BY [Original Id],
                                           PartnerProgramStatusDWKey,
                                           rn1 - rn2) AS LastUpdatedDate
FROM cte
ORDER BY [Original Id], UpdatedTime, DWKey;