softwarea
asked on
Help on difficult SQL statement needed
Hi experts,
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.
Update YourTable
set status_is_valid =
case when YourTable.ID = m.ID then 1 else 0 end
From
(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!
Ingmar
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.
Update YourTable
set status_is_valid =
case when YourTable.ID = m.ID then 1 else 0 end
From
(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!
Ingmar
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Glad I could be of any help and thanks for the grade !
ASKER
I do not know what to say: My first test was PERFECT! I hope this will hold on the next hours. But so far it is definetely an improve. And I had no idea on how to formulate this update statement. Will have a deeper look inside soon ;-)
Thanks so much!!!
Ingmar