Thread

1. Re: [SQL] locked my keys in the car

   Richard Lynch <lynch@lscorp.com> — 1998-08-03T20:11:39Z

   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
   
   
   
   

2. Re: [SQL] locked my keys in the car

   Thomas Good <tomg@q8.nrnet.org> — 1998-08-04T19:15:19Z

   Rich, Dave, Federico ---
   
   Guys, for the assist!  The last piece of the puzzle follows.
   A buddy of mine in Pennsylvania was able to force the date comparison.
   Rich, this is pretty close to your code, a couple of minor diffs
   do the job!
   
   SELECT tr_id, tr_date
   FROM crtrd1 ALIAS1
   WHERE ALIAS1.tr_unit = 'SMA'
     AND (ALIAS1.tr_type = 'A' OR ALIAS1.tr_type = 'I')
     AND NOT EXISTS(
           SELECT tr_id
           FROM crtrd1 ALIAS2
           WHERE ALIAS2.tr_unit = 'SMA'
             AND ALIAS2.tr_id = ALIAS1.tr_id
             AND (ALIAS2.tr_type = 'T' OR ALIAS2.tr_type = 'O')
             AND ALIAS2.tr_date > ALIAS1.tr_date
     )
   ORDER BY tr_date DESC;
   
   Thanks for your assistance boys - I'm thinking of getting this
   tattooed somewhere - somewhere I can see it when I need it that is!
   
       ---------- 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