Hello again...
The original post in this thread was - - -
> > What I would like to do is this:
> > SELECT id, MAX(date)
> > FROM table
> > WHERE office='M'
> > AND case='A' OR case='I';
This because, my original (badly formed) query:
SELECT id, date
FROM table
WHERE office='M'
AND case='A' OR case='I';
was returning 48,552 rows...abit more data than I needed...
Thanks to Dave Madden, James Boorn, & Federico Passaro, I reduced this
output to 12,978 rows:
SELECT id, MAX(date)
FROM table
WHERE office='M'
AND case='A' OR case='I'
GROUP BY id;
But this was still problematic, as the rows are patients and
12,978 patients (in a 15 bed ward) is a bit of overcrowding!
(Although we could use the revenue... ;-)
Next I tried Richard Lynch's suggestion:
SELECT id FROM table
WHERE office = 'M' AND (case = 'A' OR case = 'I')
ORDER BY date DESC;
And this is definitely on the right track as I am now down to
75 patients (thanks Rich).
The 13 active cases (in what we loosely term reality) are amongst
the 75 returned tuples. Moreover, I can catch the 62 inactive cases
listed amongst the output from Rich's query with:
SELECT tr_id, tr_date FROM crtrd1
WHERE tr_unit = 'SMA' AND (tr_type = 'T' OR tr_type = 'O')
ORDER BY tr_date DESC;
(In this instance, T=terminated and O=outgoing...)
So my next question is:
Is there a way, using SQL, to do the math on this, i.e., to rm the
patients who appear twice - once in the first query, once in the second?
(God forbid we re-admit the same patient at some future date!)
In other words, can I somehow go about rm'ing those patients who have
a tr_type of T or O - with a tr_date that is > the tr_date of the entry
with a tr_type of I or A?
We are getting into Byzantine complexities here, SQL wise, and I am
tempted to just dump the output to a (perl) filehandle and let perl
sort the data - but I am curious if SQL can hack it...
Thanks alot,
Tom
---------- Sisters of Charity Medical Center ----------
Department of Psychiatry
----
Thomas Good <tomg@q8.nrnet.org>
Coordinator, North Richmond C.M.H.C. Information Systems
75 Vanderbilt Ave, Quarters 8 Phone: 718-354-5528
Staten Island, NY 10304 Fax: 718-354-5056
