sql - Select only changes to a value from an audit log table -

sql - Select only changes to a value from an audit log table -

in ms sql server 2008 r2, have table foo, , every insert , update on foo, insert fooauditlog date, user, pk fooid , few of other columns of foo, including 1 numeric value, phone call cxp.

i need retrieve history of changes cxp on time given fooid. possible save foo without changing cxp, , need ignore values.

for example, if audit log entries specific foo (ie select date, user, cxp fooauditlog fooid=17) this:

date user cxp ------------------------- 10/26 fred 42 10/28 george 38 11/7 tom 38 11/9 fred 38 11/12 joe 33 11/14 tom 33 11/18 george 38

then need modify query homecoming only:

date user cxp ----------------------------- 10/26 fred 42 10/28 george 38 11/12 joe 33 11/18 george 38

and ignore entries on 11/7, 11/9, , 11/14. had considered select distinct (cpx) ... grouping date need capture entry value changes previous value.

you need previous value. version uses mysql syntax correlated subquery result:

select t.* (select t.*, (select cxp t t2 t2.date < t.date order date desc limit1 ) prevcxp t ) t prevcxp null or prevcxp <> cxp

in other databases, might utilize lag() instead of subquery, limit might replaced top or fetch first 1 row.

sql sql-server-2008 audit-tables