Matching related records from a single table

[allen.j.moore]

I have a table that tracks admission and discharges for patients.
There are 2 types of "admissions" that have to be accounted for and 2
type of "discharges"

I am trying to return a record set that matches up admissions and
discharges so I can calculate total number of days the patient was in
the facility. Here are a few sample records from the orginal data
table. (RES is patient number, CENTP is type of event (001 is
admission, 002 is discharge, 006 is readmission, 003 is death)

RES CENTP DATETM
------ ----- ------------------------------------------------------
003799 001 2005-01-01 08:00:00.000
003799 002 2005-01-21 08:00:00.000
003799 006 2005-02-01 08:00:00.000
003799 002 2005-03-21 08:00:00.000
003799 006 2005-04-01 08:00:00.000

Here is the query I am working with
--------------------
select MaxDischarge.res,cen.centp,
MAX(cen.datetm) as admitted,
MaxDischarge.centp,
MaxDischarge.datetm as discharged,
Datediff(day, max(cen.datetm), MaxDischarge.datetm) as days
from
--get all 2 between date
(select res, NF.NAME, centp, datetm from cen
join NF on NF.CLNF = cen.nf
where centp in ('002', '003') and datetm between '2001-12-01' AND
'2006-06-24'
) MaxDischarge
join cen on MaxDischarge.res = cen.res
where cen.datetm < MaxDischarge.datetm
AND cen.centp in ('001', '006')
AND MaxDischarge.res = '003799'
GROUP BY MaxDischarge.res, MaxDischarge.datetm, cen.centp,
MaxDischarge.centp
-----------------------------------
And a few of the result records
res centp admitted centp discharged days

------ ----- ----------------------- ----- ------------------------
----
003799 001 2005-01-01 08:00:00.000 002 2005-01-21 08:00:00.000 20
003799 001 2005-01-01 08:00:00.000 002 2005-03-21 08:00:00.000 79
003799 006 2005-02-01 08:00:00.000 002 2005-03-21 08:00:00.000 48
003799 001 2005-01-01 08:00:00.000 002 2005-05-21 08:00:00.000 140
003799 006 2005-04-01 08:00:00.000 002 2005-05-21 08:00:00.000 50

The problem I have is the even number records in the result set.
Notice that 1st admitted date is being returned with every other record
when I only want it once. I know I must be missing something obvious
but my brain is tired of workin on this cause I can't see it. Any help
would be appreciated.

My desired record set would look like this
res centp admitted centp discharged days

------ ----- ----------------------- ----- ------------------------
----
003799 001 2005-01-01 08:00:00.000 002 2005-01-21 08:00:00.000 20
003799 006 2005-02-01 08:00:00.000 002 2005-03-21 08:00:00.000 48
003799 006 2005-04-01 08:00:00.000 002 2005-05-21 08:00:00.000 50


Sincerely,

Allen

 

[Michel Walsh]

Hi,


SELECT a.res, a.centp, a.datetm, b.centp, b.datetm

FROM (mytable As a LEFT JOIN myTable as b
ON a.res = b.res AND a.datetm < b.datetm)
LEFT JOIN myTable as c
ON a.res = c.res AND a.datetm < c.datetm

WHERE a.centp IN(1, 6)
AND b.centp IN(2, 3)
AND c.centp IN(2, 3)

GROUP BY a.res, a.centp, a.datetm, b.centp, b.datetm

HAVING b.datetm=MIN(c.datetm)



Even if here, the LEFT JOINs are useless and can be replaced by INNER JOIN,
if you take care of the possible NULL in the HAVING clause, that would then
also list the patients still admitted, but not having left.



Hoping it may help,
Vanderghast, Access MVP

 

[crajm]

Michel,

Thank you very much, this acheived precisely what I needed.

Sincerely,

Allen