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
update Code to 2.
Here is the psuedocode:
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
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
Set Data.Code = 2
Set Data.Code = null
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.