Good morning everyone

your help would be most appreciated in the following problem I am having.

HD_OBs (FK_oid is the main link this is the Patientid)
select
oid,
fk_oid,
DIDAT as HDDate,
haloc as location,
access_used

from p_anthropometry
order by didat desc;


-- HD_Prescription (FK_oid is the main link this is the Patientid)
select
oid,
fk_oid,
HWDATE,
HWOACC,
HWOACC2

from p_hdpresc
order by hwdate desc;

-- Timeline (FK_oid is the main link this is the Patientid)
SELECT oid,
fk_oid,
TXDATE,
TXMOD,
TXREAS,
TXSUPR,
TXSITE

FROM p_timeline
order by txdate desc

I have Three tables with the above fields

HD_Obs will be the main table that will have a between parameter on the date (will be pulling monthly)
I need the other tables to link to the hd_obs table but by date.

i.e it must go to the correct Patientid but in HD_obs the date here is the main date per row
DIDAT - FK_oid - haloc -HWDATE - HWOACC TXDATE- TXREAS
02/02/2012 - 12 -- hosp - 30/12/2011 Graft 14/01/2012- Active
01/01/2012 - 12 -- hosp - 12/12/2011 Fistula 01/01/2012 - Active
25/12/2011 - 12 -- hosp - 12/12/2011 Fistula 25/12/2011 - Active

DIDAT is the main date, HWDATE must be older than the DIDAT by at least a day to be on the same row.
HWdate can be used more than once if the DIDAT is younger the HWDATE.

TXDATE can be the same date or older than DIDAT. Each table will have 3-4 extra fields to come across into each row.


The dates don’t always correspond with each other, so I need to pull the nearest one below from each table

Your Help is most appreciated, I know it should links on the FK_OID then the date but please how do I write this.
Ralph