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.)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
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?
Brian CroweDatabase AdministratorCommented:
making a few assumptions and taking a stab...

WITH cteHistory (history_id, matter_id, old_value, changed_on, rownumber)
   SELECT history_id, matter_id, old_value, changed_on,
   ROW_NUMBER() OVER(PARTITION BY matter_id, ORDER BY changed_on)
   FROM History
SELECT H0.history_id, H0.matter_id, H0.old_value,
   ISNULL(H1.old_value, (SELECT currentValue FROM liveTable)) AS new_value,
FROM cteHistory AS H0
   ON H0.matter_id = H1.matter_id
   AND H0.rownumber = H1.rownumber - 1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bjones8888PresidentAuthor Commented:
Perfect!  This works exactly as I needed.  thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.