Thread

1. locked my keys in the car

   Thomas Good <tomg@q8.nrnet.org> — 1998-07-31T18:03:28Z

   Hello all:
   
   I am unsuccessfully trying to convince PostgreSQL to use 
   aggregates illegally as I am temporarily out_of_syntax (and 
   tylenol!)
   
   What I would like to do is this:
   
   SELECT id, MAX(date)
   FROM table                     
   WHERE office='M'
   AND case='A' OR case='I';
   
   What the heck am I doing?  I wish I knew but what I am _trying_
   to do is this:
   
   I want to select all records that are current A(dmits) or
   I(ncoming) status - - - in M(y) office.  The problem is that
   multiple records exist and I need to get only the last record
   for *each* patient id.  I thought the last date would do it but 
   I can't get SQL to tolerate my bad syntax.
   (This is a case of porting a foxpro db which is very short on
   primary keys, into SQL...the original code is more confused than
   I am...)
   
   There are multiple cases that are marked `active' for each patient, 
   even in a given unit.  God knows why...
   
   Can someone translate this pseudocode into (postgre)SQL?
   
   SELECT the most recent record for each patient (based on date of entry)
   FROM accounts table
   WHERE treating_unit = 'charstr'
   AND the case is marked 'Active' or 'Incoming';
   
   Bearing in mind that the pt_id is not unique...ouch.
   
   Thanks and a free foxpro database to who ever helps me solve this!
   
       ---------- 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
   
   
   
   
   

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

   Federico Passaro <fede@link.it> — 1998-08-01T05:58:58Z

   Thomas Good wrote:
   
   > Hello all:
   >
   > I am unsuccessfully trying to convince PostgreSQL to use
   > aggregates illegally as I am temporarily out_of_syntax (and
   > tylenol!)
   >
   > What I would like to do is this:
   >
   > SELECT id, MAX(date)
   > FROM table
   > WHERE office='M'
   > AND case='A' OR case='I';
   >
   > What the heck am I doing?  I wish I knew but what I am _trying_
   > to do is this:
   >
   > I want to select all records that are current A(dmits) or
   > I(ncoming) status - - - in M(y) office.  The problem is that
   > multiple records exist and I need to get only the last record
   > for *each* patient id.  I thought the last date would do it but
   > I can't get SQL to tolerate my bad syntax.
   > (This is a case of porting a foxpro db which is very short on
   > primary keys, into SQL...the original code is more confused than
   > I am...)
   >
   > There are multiple cases that are marked `active' for each patient,
   > even in a given unit.  God knows why...
   >
   > Can someone translate this pseudocode into (postgre)SQL?
   >
   > SELECT the most recent record for each patient (based on date of entry)
   > FROM accounts table
   > WHERE treating_unit = 'charstr'
   > AND the case is marked 'Active' or 'Incoming';
   >
   > Bearing in mind that the pt_id is not unique...ouch.
   >
   > Thanks and a free foxpro database to who ever helps me solve this!
   >
   >     ---------- 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
   
     Hi Thomas,
   
   I think you should tell SQL "to get only the last record for *each*
   patient id.":
   
   SELECT id, MAX(date)
   FROM table
   WHERE office='M'
   AND case='A' OR case='I'
   GROUP BY id
   
   I hope this help, cheers
   
   federico
   
   
   

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

   Thomas Good <tomg@q8.nrnet.org> — 1998-08-03T13:10:16Z

   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
   
   
   

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

   Federico Passaro <fede@link.it> — 1998-08-03T14:01:01Z

   Thomas Good wrote:
   
   > 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
   
   Hi Thomas
   
   this should be another step toward the solution of your sql problem (I
   hope!)
   
   SELECT C1.tr_id, C2.tr_date FROM crtrd1 C1, OUTER  crtrd1 C2
   WHERE C1.tr_unit = 'SMA' AND (C1.tr_type = 'T' OR C1.tr_type = 'O') AND
   C2.tr_unit = 'SMA' AND (C2.tr_type = 'I' OR C2.tr_type = 'A') AND
   C1.tr_id = C2.tr_id AND
   C1.tr_date > C2.tr_date
   ORDER BY tr_date DESC;
   
   Cheers
   
   federico