I have a table with the following fields: history_id, matter_id, old_value, changed_on. the table is called History.
the application holds the current value of a particular field. When the user changes the value, we record what the old value was and the date/time it was changed, along with a description of the old value.
Unfortunately, when the users want to review the history, it is confusing. They see what the old value was, up until the date it was changed.
The records look like this:
1 1234 [blank] 9/20/13
2 1234 ADM 9/21/13
3 1234 BNS 9/22/13
and what is currently stored in the live record is "BOZA".
What I would like is a sql statement that will return all the 4 fields, plus another, which is what the field was changed to. For example,
1 1234 [blank] ADM 9/20/13
2 1234 ADM BNS 9/21/13
3 1234 BNS BOZA 9/22/13
Then I can show the user what it was, what it was changed to, and on what date.
Can I use a select in a case statement? If so, perhaps I could use a "Select Top 1" .... and get the record with the next highest history_id value?
I can get the current live record's value the same way, perhaps, with a select if history_id is already the highest value.
(yes, I recognize this was poor design, but it's what I have to work with.)