Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47

    Unanswered: really need help with a query

    Really stuck on a query...
    i have two tables :
    patient_details and patient_notes

    table patient_details:
    id
    forename
    surname

    table patient_notes:
    code
    term
    id
    date
    ---------------

    i need to select the patient id,term and date based upon a like select of codes.
    I cant do this ok... and i have for example:
    patient_id | code| term | date
    ----------------------------------------------------------
    1 1ha broken toe 03/01/2003
    1 1hb back ache 02/01/2003
    1 1hc strain to back 01/01/2003


    if there are any duplicates of the same term and code i can omit these, but i only need to select the most recent one by date.
    how can i achieve this?
    my code:

    SELECT PATIENT_DETAILS.PATIENT_ID, PATIENT_NOTES.CODE, PATIENT_NOTES.TERM, Max(PATIENT_NOTES.DATE_) AS MaxOfDATE_
    FROM PATIENT_NOTES INNER JOIN PATIENT_DETAILS ON PATIENT_NOTES.PATIENT_ID = PATIENT_DETAILS.PATIENT_ID
    GROUP BY PATIENT_DETAILS.PATIENT_ID, PATIENT_NOTES.CODE, PATIENT_NOTES.TERM
    HAVING (((PATIENT_NOTES.CODE) Like "1*" And (PATIENT_NOTES.CODE) Not Like "2*")) OR (PATIENT_NOTES.CODE)="1ha")or (PATIENT_NOTES.CODE)="1hb")or (PATIENT_NOTES.CODE)="1hc")))
    ORDER BY PATIENT_DETAILS.PATIENT_ID, Max(PATIENT_NOTES.DATE_) DESC;
    so i get :
    patient_id | code | term | date
    ----------------------------------------------------------
    1 1ha broken toe 03/01/2003
    Many thanks dave
    Last edited by davieboy_xr; 04-09-04 at 11:58.

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    Assuming your given table structures,

    > table patient_details:
    > id
    > forename
    > surname
    >
    > table patient_notes:
    > code
    > term
    > id
    > date

    Try this:

    SELECT p1.*
    FROM patient_notes AS p1
    WHERE p1.date=
    (SELECT MAX(p2.date) FROM patient_notes AS p2 WHERE p2.id=p1.id AND p2.code=p1.code;);

    This will return the most recent date for a given code for a given patient ID.

    Alternatively, if you simply wish the most recent record for a patient, regardless of the code, you can use:

    SELECT p1.*
    FROM patient_notes AS p1
    WHERE p1.date=
    (SELECT MAX(p2.date) FROM patient_notes AS p2 WHERE p2.id=p1.id;);


    Let's say the patient_notes table has the following:
    patient_id | code| term | date
    ----------------------------------------------------------
    1 1ha broken toe 03/01/2003
    1 1hb back ache 02/01/2003
    1 1hc strain to back 01/01/2003
    1 1hb back ache 02/01/2002


    The first query will return the following result:
    patient_id | code| term | date
    ----------------------------------------------------------
    1 1ha broken toe 03/01/2003
    1 1hb back ache 02/01/2003
    1 1hc strain to back 01/01/2003

    The second query will return the following result:
    patient_id | code| term | date
    ----------------------------------------------------------
    1 1ha broken toe 03/01/2003

  3. #3
    Join Date
    Nov 2003
    Location
    uk
    Posts
    47
    thanks matthew that works a treat, the way i am able to to do it is create a query that filters out all the codes i want ;then i run the query to select the codes by the last date.. Works and does exactly what i want, but now i need to have the two process as just one query. Is this possible?
    Heres what my code looks like that filters out the codes:I called this table Diagnosis

    SELECT PATIENT_DETAILS.PATIENT_ID, PATIENT_NOTES.CODE, PATIENT_NOTES.TERM, PATIENT_NOTES.DATE_
    FROM PATIENT_NOTES INNER JOIN PATIENT_DETAILS ON PATIENT_NOTES.PATIENT_ID = PATIENT_DETAILS.PATIENT_ID
    GROUP BY PATIENT_DETAILS.PATIENT_ID, PATIENT_NOTES.CODE, PATIENT_NOTES.TERM, PATIENT_NOTES.DATE_
    HAVING (((PATIENT_NOTES.CODE) Like "B*" And (PATIENT_NOTES.READ_CODE) Not Like "B7*")) OR (((PATIENT_NOTES.CODE)="B585.")) OR (((PATIENT_NOTES.CODE)="4M5.."))
    ORDER BY PATIENT_DETAILS.PATIENT_ID, PATIENT_NOTES.DATE_ DESC;
    --------
    And heres the code for sorting the data(Cheers mat)

    SELECT p1.PATIENT_ID, p1.DATE_ AS Expr, p1.CODE, p1.TERM
    FROM DIAGNOSIS AS p1 INNER JOIN PATIENT_DETAILS ON p1.PATIENT_ID = PATIENT_DETAILS.PATIENT_ID
    WHERE (((p1.DATE_)=(SELECT MAX(p2.date_) FROM DIAGNOSIS AS p2 WHERE p2.PATIENT_id=p1.PATIENT_id))
    GROUP BY p1.PATIENT_ID, p1.DATE_, p1.CODE, p1.TERM;

    Help much appriciated thanks in advance..

  4. #4
    Join Date
    Feb 2004
    Posts
    137
    Try this:

    SELECT PATIENT_DETAILS.PATIENT_ID, PATIENT_NOTES.CODE, PATIENT_NOTES.TERM, PATIENT_NOTES.DATE_
    FROM PATIENT_NOTES INNER JOIN PATIENT_DETAILS ON PATIENT_NOTES.PATIENT_ID = PATIENT_DETAILS.PATIENT_ID
    WHERE PATIENT_NOTES.DATE_=(SELECT MAX(P2.DATE_) FROM PATIENT_NOTES AS P2 WHERE P2.PATIENT_ID=PATIENT_NOTES.PATIENT_ID;)
    GROUP BY PATIENT_DETAILS.PATIENT_ID, PATIENT_NOTES.CODE, PATIENT_NOTES.TERM, PATIENT_NOTES.DATE_
    HAVING (((PATIENT_NOTES.CODE) Like "B*" And (PATIENT_NOTES.READ_CODE) Not Like "B7*")) OR (((PATIENT_NOTES.CODE)="B585.")) OR (((PATIENT_NOTES.CODE)="4M5.."))
    ORDER BY PATIENT_DETAILS.PATIENT_ID, PATIENT_NOTES.DATE_ DESC;

Posting Permissions

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