Hi Team,
I have some data that changes and gets an update date whenever a field is changed.
I am trying to report when a row has changed.
I can get pretty close if I take out the update date using a group by in conjunction with a dedup query but am not convinced I am getting the correct values for the changed row for
update date and updatedby as I had to use the "last" function in the group by query.
Some sample data is below.
ID Name Update_Date Updated by Status Glob_App_Class Simp_act_ret_date Simp_class Display 9117 App 2014-02-10 11:12:18 714 Act Inter_Plat Maintain 9117 App 2014-02-28 17:45:07 118 Act Inter_Plat Strategic Y 9117 App 2014-03-15 07:02:14 859 InAct Invest Strategic Y 9117 App 2014-03-15 08:39:03 859 InAct Invest Strategic 9117 App 2014-03-19 00:51:52 880 Act Dep Strategic Y 9117 App 2014-03-28 09:15:58 714 Act Dep Retire Y 9117 App 2014-03-31 12:43:27 118 Act Inter_Plat Deprecated/Retire Y 113 Bridge 2014-02-20 10:52:36 252 Act Dep Invest 113 Bridge 2014-02-28 17:44:16 118 Act Dep Strategic Y 113 Bridge 2014-03-07 01:00:03 system Act Dep Strategic 113 Bridge 2014-03-07 09:01:16 system InAct Dep Strategic Y 113 Bridge 2014-03-08 09:01:13 system Act Inter_Plat Strategic/Invest Y
Currently I have the output as a spreadsheet format but ideally want it in the format below which looks to me like a crosstab but nothing I can do gets the crosstab to look like this.
any help appreciated.
ID Name Update_Date Updated by Status Field Old Val New Val 9117 App 2014-02-28 17:45:07 118 Act Simp_Class Maintain Strategic 9118 App 2014-03-15 07:02:14 119 InAct Glob_app_Class Inter_Plat Invest 9117 App 2014-03-19 00:51:52 880 Act Glob_app_Class Invest Dep