Help on difficult SQL statement needed
Posted on 2007-10-02
I have the following table structure:
- Status_ID, bigint, PK
- Status_Name, varchar(50)
- Status_Date, DateTime
- Status_Is_Valid, bit
And here is the trick: only the LATEST status may be valid (Status_Is_Valid = true). All other status have to be invalid (Status_Is_Valid = false). In other word: on every INSERT and UPDATE I have to make sure that Status_Is_Valid is fixed for all records.
A few days ago I already got excellent help by Dennis Miller (dgmg). Thanks for that again, Dennis!
He suggested me to use the following statement to make sure that only one of my status entries is valid.
set status_is_valid =
case when YourTable.ID = m.ID then 1 else 0 end
(Select max(ID) from YourTable T
where T.status_date =
(select max(status_date from YourTable)) m
This seemed to work great in the beginning, but now I found out that this is still not enough: SQL server gets confused if there are two or more status with the same MAX(status_date) - and unfortunately this can happen. What I need here is a second criteria that takes over in case there is more than one MAX(status_date) status, let's say Status_ID. So, in other words: if there is more than one status having MAX(status_date) then set the one with MAX(status_id) to valid. Can this be achieved in some way by modifying the aboves sql statement?
Hope anybody of you can help me out of this again. Thanks so much!