imanym
asked on
Query data based on the next date
I have a record of dated occurrences. For example:
On 01/01/08 John Doe was discharged from the hospital
On 01/05/08 John was assessed
On 02/06/08 John was discharged from the hospital and assessed again 03/16/08
I need to match one assessment to 1 discharge without crossing so the >= will not work. I am trying to rate compliance with the formula of an assessment occurring within 7 days of the discharge. The difficult part is that no assessment is uniquely linked to an event. So I am trying to Query for an occurrence after the discharge date.
On 01/01/08 John Doe was discharged from the hospital
On 01/05/08 John was assessed
On 02/06/08 John was discharged from the hospital and assessed again 03/16/08
I need to match one assessment to 1 discharge without crossing so the >= will not work. I am trying to rate compliance with the formula of an assessment occurring within 7 days of the discharge. The difficult part is that no assessment is uniquely linked to an event. So I am trying to Query for an occurrence after the discharge date.
chapmandew
post your table structure.
ASKER
There is no pk located within the table in order to link an occurrence with and occurrence to the other table.
I am not sure what you mean by the table structure?
there is no pk/fk in either of these tables to perform a link by event only by member. So I am trying to get the discharge_date closest to the assessment date and use that to quality my formaula of being less than or greater than 10 days
SELECT a.patient,a.lname,a.fname,
from discharges AS a
LEFT JOIN
assessments AS b
ON a.patient = b.patient
AND a.assessment_date<= b.discharge_date + 10
I am not sure what you mean by the table structure?
there is no pk/fk in either of these tables to perform a link by event only by member. So I am trying to get the discharge_date closest to the assessment date and use that to quality my formaula of being less than or greater than 10 days
SELECT a.patient,a.lname,a.fname,
from discharges AS a
LEFT JOIN
assessments AS b
ON a.patient = b.patient
AND a.assessment_date<= b.discharge_date + 10
ASKER
There is no pk located within the table in order to link an occurrence with and occurrence to the other table.
I am not sure what you mean by the table structure?
there is no pk/fk in either of these tables to perform a link by event only by member. So I am trying to get the discharge_date closest to the assessment date and use that to quality my formaula of being less than or greater than 10 days
SELECT a.patient,a.lname,a.fname,
from discharges AS a
LEFT JOIN
assessments AS b
ON a.patient = b.patient
AND a.assessment_date<= b.discharge_date + 10
I am not sure what you mean by the table structure?
there is no pk/fk in either of these tables to perform a link by event only by member. So I am trying to get the discharge_date closest to the assessment date and use that to quality my formaula of being less than or greater than 10 days
SELECT a.patient,a.lname,a.fname,
from discharges AS a
LEFT JOIN
assessments AS b
ON a.patient = b.patient
AND a.assessment_date<= b.discharge_date + 10
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.