Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2010
    Posts
    6

    Question Unanswered: Update iif all previous records < X

    Hello fellow SQLers,
    I'm trying to write a query that seems to be a bit more complicated than I had expected. Here is the general idea:

    I have a table called Data that contains hundreds of patients information.
    There is a field called Code that I would like to update based on the following conditions.
    If a patient's Dx from the previous visit was 1 and now it is 2 or 3 then
    If a patient's Prescription from ALL previous visits was null then
    update Code to 1
    Else
    update Code to 2.

    Here is the psuedocode:
    Update Data
    Set Data.Code = iif(previous Data.Dx = 1 and current Data.Dx = 2 or 3,
    iif(first thru previous Data.Rx = null, 1, 2), null)


    What I'm having trouble with is how do I get the previous data for each patient using SQL? Basically it needs to return records with an appointment date prior to the current record's date..... not sure how to do this

    Any pointers are helpful
    Cheers
    .B.

  2. #2
    Join Date
    Aug 2010
    Posts
    6
    No one?

    Hmmm.. ok, here is a fictitious sample table called Data:

    Row___ Patient ID___ Visit_____ Dx_____ Rx_____ Code
    1 _______ 55 _____ 5/5/2007 ___ 1 _____ 4
    2 _______ 55 _____ 1/4/2008 ___ 1 _____ 4
    3 _______ 77 _____ 1/4/2005 ___ 1 _____
    4 _______ 77 _____ 7/30/2005 __ 1
    5 _______ 77 _____ 3/14/2006 __ 2 _____ 1 _____ 1
    6 _______ 77 _____ 2/1/2007 ___ 2 _____ 1
    7 _______ 99 _____ 1/5/2008 ___ 2 _____ 4
    8 _______ 55 _____ 1/23/2009 __ 3 _____ 4 _____ 2
    9 _______ 99 _____ 7/18/2008 __ 2 _____ 4
    10 ______ 99 _____ 3/13/2009 __ 3 _____ 4
    11 _______ 77 _____ 9/4/2007 ___3 _____1
    12 _______ 55 _____ 8/30/2009 __ 3 _____ 4
    13 _______ 77 _____ 5/3/2008 ___ 3 _____ 1
    14 _______ 99 _____ 9/10/2009 __ 3 _____ 4

    I need an update query that goes line by line and checks:
    If the previous Dx for that patient was 1 and the current Dx is 2 or 3 then
    If ALL previous Rx was null then
    Set Data.Code = 1
    Else
    Set Data.Code = 2
    End if
    Else
    Set Data.Code = null
    End if

    What I can't figure out is how to reference the previous data for a patient in a query....

    Here is an example: Say the query is on row 8. (patient ID = 55, Visit date = 1/23/2009). The previous entry for patient 55 was row 2. The Dx of patient 55 on row 2 was 1.... and the Dx of patient 55 on the current row (8) is 3. So the first If statement is true. Now the 2nd if statement (If all previous Rx was null). The other previous tests for patient 55 are on rows 1 and 2. The Rx for rows 1 and 2 are 4 and 4, which does not = null. Therefore, the else Data.Code of row 8 = 2.

    Its complicated...perhaps I'm going about it the wrong way... maybe I can use WHERE statements instead of iif...but the problem with that is I want to update code with 3 different possibilities (1, 2, or null) based on a condition..that is why I'm trying to use iif.

    Thanks!!

Posting Permissions

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