Received: from lscorp.com (poohbah.lscorp.com [207.152.64.2]) by hub.org (8.8.8/8.7.5) with ESMTP id QAA00857 for ; Mon, 3 Aug 1998 16:13:21 -0400 (EDT) Received: from [207.152.64.133] by lscorp.com with SMTP (Eudora Internet Mail Server 1.2); Mon, 3 Aug 1998 15:11:39 -0500 X-Sender: lynch@poohbah.lscorp.com Message-Id: Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" To: Thomas Good , pgsql-sql@postgreSQL.org From: lynch@lscorp.com (Richard Lynch) Subject: Re: [SQL] locked my keys in the car Cc: UserGuideDog Date: Mon, 3 Aug 1998 15:11:39 -0500 At 9:10 AM 8/3/98, Thomas Good wrote: >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!) Well I'm confused. Nothing new there, eh? If all you want is active cases, why not: select id from table where office = 'M' and case = 'A' order by date desc; >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? You should be able to just mush all the stuff together in something like this select distinct table.id from table, crtrd1 as entry, crtrd1 as exit where table.office = 'M' and (table.case = 'A' or table.case = 'I') and entry.tr_id = exit.tr_id and entry.tr_unit = 'SMA' and exit.tr_type != 'T' and exit..tr_type != 'O' and exit.tr_date > entry.tr_date and (entry.tr_type = 'A' or entry.tr_type = 'I'); I *THINK* this is kinda what you have asked for, but I don't really understand for sure what your tables are, nor what you want to get out of them... -- -- -- "TANSTAAFL" Rich lynch@lscorp.com