Identifying specific changes via audit table

Question

I have an audit table in a SQL database that is structured like the one below:

audit_id	id	location	location_sub	location_status	dtm_utc_action	action_type
2144	2105	9	1	1	2022-09-08 12:36	i
4653	2105	9	1	1	2022-09-08 13:53	u
7304	2105	10	2	2	2022-09-13 15:51	u
7326	2105	11	1	2	2022-09-14 10:06	u

I'm trying to write a query that will assist me in finding records from this table where the location has changed, and would like to show the ID, old location, new location and time of the change.

Based on some other questions, I've tried joining the table with itself, but I don't understand how to prevent duplication. This query below will return each record multiple times, which isn't desired.

SELECT
    a.id,
    a.location       AS old_loc, 
    a.dtm_utc_action AS FirstModifyDate,
    b.location       AS new_loc, 
    b.dtm_utc_action AS SecondModifyDate  

FROM
    audit_table a
    JOIN audit_table b ON a.id = b.id

WHERE
    a.audit_id <> b.audit_id
    AND
    a.dtm_utc_action < b.dtm_utc_action
    AND
    a.location <> b.location
    AND
    a.id = '2105'

Would like results to look like this:

id	old_loc	new_loc	dtm_utc_action
2105	9	10	2022-09-13 15:50
2105	10	11	2022-09-14 10:06

Answer 1

Depending on your DBMS you can use a LEAD function to retrieve the next date from the audit table

with audit_sequence as (
    select *, lead(dtm_utc_action) OVER ( PARTITION BY id ORDER BY dtm_utc_action) next_update
    from audit_table 
) 

SELECT
    a.id,
    a.location       AS old_loc, 
    a.dtm_utc_action AS FirstModifyDate,
    b.location       AS new_loc, 
    b.dtm_utc_action AS SecondModifyDate  

FROM
    audit_table a
    JOIN audit_sequence s on a.id = s.id and a.dtm_utc_action = s.dtm_utc_action
    JOIN audit_table b on a.id = b.id and b.dtm_utc_action = s.next_update
WHERE
    a.location <> b.location
    AND
    a.id = '2105'