Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!swrinde!mips!pacbell.com!pacbell!rtech!ingres!ingres.com!jpk
From: jpk@ingres.com (Jon Krueger)
Newsgroups: comp.databases
Subject: Re: SQL Question
Message-ID: <1990Oct10.013533.949@ingres.Ingres.COM>
Date: 10 Oct 90 01:35:33 GMT
References: <391@ulticorp.UUCP>
Lines: 47

From article <391@ulticorp.UUCP>, by mikei@ulticorp.UUCP (mikei):
> Given a table of patients PID int   WEIGHT int
> where one PID may have numerous weight readings.
> How would I select the PID for only those patients whose
> weight was ALWAYS <= 100.

Or "find patients whose weight has never exceeded 100 pounds", or "find
patients whose weight is never known to have exceeded 100 pounds":

	select pid from patients where pid not in
		(select pid from patients where weight > 100)

Another entertaining query is "find patients who have experienced
significant weight changes".  We might deem significant any change of
10 pounds or more between two successive weighings:

	/* assumes not more than one weight entered per patient per day */
	select p.pid, p.date, p.weight, p2.date, p2.weight
		from patients p, patients p2
	where abs(p2.weight - p.weight) >= 10
		and p.pid = p2.pid and p.date < p2.date
	and not exists (select p3.pid from patients p3 where
		p3.date > p.date and p3.date < p2.date)

Of course, it might have been years between weighings, a factor which
this query does not take into account.  On the other hand, a simple
graph of weight as a function of date might prove more useful to the
clinician than all the crafting we might put into refining this query.
The database might more productively be used to validate data entry
based on recent history:

	exec sql select weight into :last_wt,
	name into :nm, date into :dt from patients
	where pid = :this_pid and date = 
		(select max(date) from patients where pid = :this_pid);
	if (abs(last_wt - this_wt) > 10) {
		printf("%s weighed %d pounds on %s.  ", nm, last_wt, dt);
		printf("Are you *sure* %s now weighs %d pounds?",
			nm, this_wt);
	}

-- Jon

Does not necessarily reflect the views of Ingres Corp.
--

Jon Krueger, jpk@ingres.com