Urgent SQL question

kellen · #1 (**permalink**) 08-28-06, 06:05

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.

r937 · #2 (**permalink**) 08-28-06, 07:45

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

Pat Phelan · #3 (**permalink**) 08-28-06, 08:20

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

Peter.Vanroose · #4 (**permalink**) 08-30-06, 15:24

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.

kellen · #5 (**permalink**) 08-31-06, 22:14

Thanks Peter. That was what i was actually looking for.

Regards Kellen

Internet Services

Web Development

Digital Marketing

Consumer Tech