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

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
   (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!
  • 2
1 Solution
Try This:

update  YourTable
set     Status_Is_Valid = case
    when (yourtable.status_id in (
        Select  max(T.Status_ID) as ID
        from    YourTable T
        where   T.status_date =
        (   select max(status_date)
            from   YourTable
            where  Status_Name = T.Status_Name)
        group  by Status_name
        )) then 1
    else 0

Hope this helps ...
softwareaAuthor Commented:
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!!!
Glad I could be of any help and thanks for the grade !

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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