🚀 go-pugleaf

On Sun, 13 Sep 1998 19:59:53 GMT, hartkamp@uni-duesseldorf.de (Norbert
Hartkamp) wrote:

[...]

>Thank you for your reply. Maybe you're right, but mayby it's true only
>after you've mastered all the secrets of the SELECT statment.
>
>For example: I have a table (called "WEEK") with certain data from
>patients, that stay in a hospital typically between 3 and 6 month.
>Data in this table are gathered weekly. I have another table in which
>there are other data (SCL-90-R in that case) gathered in 4 week-
>intervals. All patients have a unique number "DSNR". Both tables of
>course have a Date-field (called "DATUM). How can I build a
>SQL-statement that gives me in each row:
>
>DSNR, admission date ( MIN(week.datum) ?? ), discharge date 
>( MAX(week.datum) ?? ), SCL.scores from the  first week (same date as
>MIN(week.datum) ) and scl.scores from MAX(week.datum).
>
>In addition I would like to have the scl-scores from that date, that
>is closest to mid-treatmen, where mid-treatment can be computed as 
>
>MIN(week.datum)+((MAX(week.datum)-MIN(week.datum))/2).
>
>Any idea (if I suceeded to make clear, what I need)?

In SQL, there are no functions or operators that use the exclusively human
unit of measure called "week". Because such a feature is not in standard
SQL, you will not find it in local SQL either.

However, you can often simulate such operations. In SQL you can perform
arithmetic operations on DATE and TIMESTAMP values. For example, if you add
three to a DATE value, the result is a date three after the original date.
Expanding on this, if you add 7 it is a week later, 14 is two weeks later,
and so on. So you could supply to an SQL statement a date that is the
beginning of a date range and then add 7, 14, 21, or 28 to this value to
come up with an ending date for the range that is n number of weeks after
the starting date.

Now, due to the way SQL works, you can retrieve values from records that
meet a certain criteria or set of criteria. But you cannot retrieve with
the same SQL statement data that meets one criteria and also data that
meets another criteria but not the first criteria. To work with this, you
can combine multiple SELECT statements, each with different criteria, using
a UNION join. For example, in the statement below, the first SELECT query
retrieves rows from a table where a DateCol column contains a date between
the start date of "1/1/1998" (represented by a parameter) and a date one
week later. The second SELECT query retrieves rows where that date column's
value is between the start date plus seven and the start date plus 14
(i.e., in the second week after the start date). The UNION join combines
the result set from both SELECT queries into a single result set.

  SELECT *
  FROM Week
  WHERE (AdmissionDate BETWEEN :StartDate AND :StartDate + 7)
  UNION
  SELECT *
  FROM Week
  WHERE (AdmissionDate BETWEEN :StartDate + 7 AND :StartDate + 14)

Another aspect of your situation is the fact that data is broken out over
multiple tables (data normalization -- a good thing). You put data from
multiple tables into a single result set using a join, typically either an
inner or an outer join. Here is an example of an inner join.

  SELECT W.*, S.*
  FROM Week W
    INNER JOIN SCL-90-R S
      ON (W.DSNR = S.DSNR)

The ON condition is what matches up the rows from one table (Week) with the
corresponding rows from the other table (SCL-90-R).

When performing aggregate operations on columns, the columns in the SELECT
clause must meet a very specific criteria. To be in the SELECT clause, a
column _must_ either be the subject of an aggregate function (such as MIN
and MAX) *or* also be listed in a GROUP BY clause. For example:

  SELECT W1.DSNR, MAX(W1.AdmissionDate), MIN(W1.Datum),
    MAX(W1.Datum), MAX(S1.Score)
  FROM Week W1
    INNER JOIN SCL-90-R S1
      ON (W1.DSNR = S1.DSNR)
  GROUP BY W1.DSNR

Now, you should be able to combine all of these aspects (week-based
filtering, joining tables, and aggregate functions) into a single
statement. Something like:

  SELECT W1.DSNR, MAX(W1.AdmissionDate), MIN(W1.Datum),
    MAX(W1.Datum), MAX(S1.Score)
  FROM Week W1
    INNER JOIN SCL-90-R S1
      ON (W1.DSNR = S1.DSNR)
  WHERE (W1.AdmissionDate BETWEEN :StartDate AND :StartDate + 7)
  GROUP BY W1.DSNR
  UNION ALL
  SELECT W2.DSNR, MAX(W2.AdmissionDate), MIN(W2.Datum),
    MAX(W2.Datum), MAX(S2.Score)
  FROM Week W2
    INNER JOIN SCL-90-R S2
      ON (W2.DSNR = S2.DSNR)
  WHERE (W2.AdmissionDate BETWEEN :StartDate + 7 AND :StartDate + 14)
  GROUP BY W2.DSNR
  ...
  ORDER BY 1

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)