How to create a column that captures record changes?

Question

I have a table with customer records. When changes occur to records, the old one is kept and 'closed out' by updating end_Dt and Current_Flag, then a new record is added with the appropriate changes.

See example below:

Cust_ID	Name	Age	Phone type	Start_Dt	End_Dt	Current_Flag
12345	Bob	8	apple	12/02/22	01/15/23	N
12345	Bob	12	Samsung	01/16/22	12/31/99	Y
65739	John	44	Samsung	02/10/22	03/01/22	N
65739	John	44	Google	03/02/22	12/31/99	Y

I'm developing a Change Report and need help creating a change column. Hoping the report would look like:

Cust_ID	Name	Change field
12345	Bob	Age, Phone type
65739	John	Phone type

I'm trying to accomplish this with SQL, or in Tableau Prep / Prep Builder.

I've tried to create flags variables for all fields, but have not had success with this effort. I've also tried a few CASE statements but have not had success.

Please format your data visualization, it's very hard to understand what you are asking — godot, Mar 03 '23 at 21:08
Are you only looking at the changes between the current record and the previous record, the current record and all previous records - or something else? — NickW, Mar 03 '23 at 22:15
Is there only ever one 'N' and one 'Y' row per `cust_id`? If not, are you comparing all of the 'N' ones to the 'Y' one or are you comparing the most recent 'N' one to the 'Y' one? — EdmCoff, Mar 03 '23 at 23:03

score 0 · Answer 1 · answered Mar 03 '23 at 23:44

I think you are looking for something along the lines of

SELECT t1.cust_id, t1.name, string_agg(c.change_col, ', ') change_col
FROM test t1 INNER JOIN test t2
  ON t1.cust_id = t2.cust_id AND t1.name = t2.name
 CROSS APPLY (SELECT 'Age' change_col WHERE t1.age <> t2.age UNION ALL SELECT 'Phone type' WHERE t1.phone_type <> t2.phone_type) c
WHERE t1.current_flag = 'Y'
 AND t2.current_flag = 'N'
GROUP BY t1.cust_id, t1.name

You can see it working in this Fiddle.

The basic idea is to get the current row (t1) and the row you want to compare to (t2), and then cross apply to generate a row for each column that doesn't match. The string_agg in combination with the group by then concatenates the list of columns that have changed. To add another column you want to check for a difference, you would add it to the the cross apply with a union all. For example, you could add UNION ALL SELECT 'my new column name' WHERE t1.mynewcolumn <> t21.mynewcolumn.

string_agg is DBMS-specific, but you can use list_agg or whatever is appropriate for your DBMS.

If there are multiple older rows, you will need to add logic to get the two your care about comparing.

In that same Fiddle, I also did an example without the cross apply/group by/string_agg. This works fine for two columns (and might be easier to follow), but you can see it will be much harder to maintain if you want to add more columns to check for comparison in the future.

I think this will work- Thank you so much! – Benjamin Mar 04 '23 at 22:24 — Benjamin, Mar 04 '23 at 22:24

score 0 · Answer 2 · answered Mar 04 '23 at 01:06

You can use the lead function to check if the value of (age or phone) is changed then aggregate as the following:

select Cust_ID, Name,
  concat_ws(', ', max(age_flag), max(phone_flag)) as Change_field
from
(
  select *,
    case 
      when age <> lead(age, 1, age) over (partition by cust_id order by start_dt) then 'Age' 
    end as age_flag,
    case 
      when Phone_type <> lead(Phone_type, 1, Phone_type) over (partition by cust_id order by start_dt) then 'Phone type' 
    end as phone_flag
  from tbl_name
) T
group by Cust_ID, Name
order by Cust_ID, Name

When there is no change for a value, the case expression will return a null value; the max function ignores null values, if there are no rows to select (all rows have no change), the max will return a null value, here, the concat_ws function will ignore the null values and concatenate only max functions with a value.

see demo

How to create a column that captures record changes?

2 Answers2