Link to home
Start Free TrialLog in
Avatar of bjones8888
bjones8888Flag for United States of America

asked on

SQL select problem

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.)
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

What is matter_id?  How would one connect your live table to the history table?  Is there a only one live/history table at issue?
ASKER CERTIFIED SOLUTION
Avatar of Brian Crowe
Brian Crowe
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bjones8888

ASKER

Perfect!  This works exactly as I needed.  thank you!