• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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.)
  • 2
1 Solution
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
bjones8888PresidentAuthor Commented:
Perfect!  This works exactly as I needed.  thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now