Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2005
    Posts
    12

    Unanswered: A little help..PLEASE

    I have been working on this for a while and just can't seem to figure out a way to make it work. Let me explain what I need to do. I have 2 tables, Patient and PatientCharges. There is one record for each patient in the Patient table, but there may be multiple records per patient in the patient charges table, representing each time the patient has visited the doctor. What I need to do is find the oldest date of service for each patient in the patientcharges table and place that value in the first visit date field for each patient in the patient table.

    Patient Table
    Patient_number Last_Name First_Name First_Visit_Date
    108 Drews Mike 01/01/1899
    205 Samms Deb 01/01/1899


    Patient Charges Table
    Patient_number Date_of_Service
    108 01/15/1996
    108 01/29/1996
    108 02/15/1996
    108 10/01/1997
    205 05/10/1998
    205 06/12/1998
    205 07/20/2000

    Thanks in advance for all help. It is greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is poor design, and you'll regret doing it if you use the system very long.

    With that said, the solution is simple.
    Code:
    UPDATE Patient
       SET date_first_visit = (SELECT Min(date_of_service)
          FROM PatientCharges
          WHERE 0 = PatientCharges.PatientNumber - Patient.PatientNumber)
    -PatP

  3. #3
    Join Date
    Nov 2002
    Posts
    272
    Quote Originally Posted by Pat Phelan
    Code:
          WHERE 0 = PatientCharges.PatientNumber - Patient.PatientNumber
    Why do you write 0 = a - b instead of a = b ?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Read his post. He said it was a poor design, didn't he?

    Sorry Pat, couldn't resist....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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