Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    2

    Unanswered: Urgent SQL question

    Hi,

    I have 2 tables record patient id, inpatient and outpatient date.
    Out_Patient table:

    p_id, out_date
    001, 2001/1/1
    001, 2002/2/15
    001, 2002/3/7
    002, 2002/6/6
    002, 2004/5/20
    003, 2003/7/8

    In_Patient table:

    p_id, In_date, Last_Out_date
    001, 2000/12/21
    001, 2002/2/10
    001, 2002/3/5
    002, 2002/6/1
    002, 2004/5/18
    003, 2003/7/7

    When a patient comes in, I need to retrieve his previous out_date and update it in the Last_Out_date Column. So the result of In_Patient table suppose to be:

    p_id, In_date, Last_Out_date
    001, 2000/12/21
    001, 2002/2/10, 2001/1/1
    001, 2002/3/5, 2002/2/15
    002, 2002/6/1
    002, 2004/5/18, 2002/6/6
    003, 2003/7/7

    I've used min() function but didn't work the way I wanted. I'd appreciate if anyone could tell me how to do this.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    update In_Patient 
       set Last_Out_date = t2.out_date
      from In_Patient t1
    inner
      join Out_Patient t2
        on t1.p_id = t2.p_id
     where t2.out_date 
         = ( select max(out_date)
               from Out_Patient
              where t2.p_id )
    please note that the UPDATE... FROM... syntax is for microsoft sql server
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    please note that the UPDATE... FROM... syntax is for microsoft sql server
    While not all SQL dialects implement this handy non-standard construct, it isn't just a Microsoft feature...

    -PatP

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Or without this "nonstandard" feature:
    (at least, if I understand the problem correctly)
    Code:
    UPDATE In_Patient AS t1
       SET Last_Out_date = (SELECT max(out_date)
                            FROM   Out_Patient
                            WHERE  p_id = t1.p_id
                              AND  out_date < t1.in_date)
    Note that nothing is set when the subquery finds no out_dates older than in_date, which is exactly what is needed.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Aug 2006
    Posts
    2
    Thanks Peter. That was what i was actually looking for.

    Regards Kellen

Posting Permissions

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