Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Update Query with MAX(date)

    Thanks in advance. I need a bit of help with an update query

    I need to write an UPDATE query that sets isFinalHold = 1 WHERE the HoldCloseDate is the MAX for each ClaimID. My try is below the sample data.

    Sample Data (for the sample data holdIDs 698 and 699 would have isFinalHold = 1 and the others would be 0)

    holdID ClaimID LineID HoldName HoldCloseDate IsFinalHold
    695 6 1 COBhold 1/02/09
    696 6 2 EOBhold 1/4/09
    697 6 2 COBhold 1/2/09
    698 6 3 Memberhold 1/5/09
    699 7 0 Dochold 1/4/09

    UPDATE tblHOLD
    SET isFinalHold = 1

    UPDATE tblHOLD
    SET isFinalHold = (select MAX(HoldCloseDate), ClaimID from
    tblHOLD fl where fl.holdID = tblHOLD.holdID)
    WHERE holdID = f1.holdID

    Appreciate the look.

    Ray

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    UPDATE tblhold
    SET    isfinalhold = 1
    FROM   tblhold As [t]
     INNER
      JOIN (
            SELECT claimid
                 , Max(holdclosedate) As [max_date]
            FROM   tblHold
            GROUP
                BY claimid
           ) As [a_subquery]
        ON t.claimid = a_subquery.claimid
       AND t.holdclosedate = a_subquery.max_date
    That should do it
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And this one will do both the updates at once
    Code:
    UPDATE tblhold
    SET    isfinalhold = CASE WHEN a_subquery.claimid IS NULL THEN 0 ELSE 1 END)
    FROM   tblhold As [t]
     LEFT
      JOIN (
            SELECT claimid
                 , Max(holdclosedate) As [max_date]
            FROM   tblHold
            GROUP
                BY claimid
           ) As [a_subquery]
        ON t.claimid = a_subquery.claimid
       AND t.holdclosedate = a_subquery.max_date
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2004
    Posts
    57

    Thanks

    George.
    Thx that did it...

    Ray

  5. #5
    Join Date
    Dec 2008
    Posts
    135
    hi rkobs,
    once try this
    declare @tab table (holdID int, ClaimID int, LineID int, HoldName varchar(32), HoldCloseDate datetime,IsFinalHold bit)
    insert into @tab select 695, 6, 1, 'COBhold', '1/02/09',null union all
    select 696, 6, 2, 'EOBhold','1/4/09',null union all
    select 697, 6, 2, 'COBhold', '1/2/09',null union all
    select 698, 6, 3, 'Memberhold', '1/5/09',null union all
    select 699, 7, 0, 'Dochold','1/4/09',null

    update t
    set isfinalhold = 1
    from @tab t
    where holdclosedate = (select max(HoldCloseDate) from @tab where claimid = t.claimid)

    select * from @tab

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •