Re: One-to-many relationship, non-equality join criteria. Can't come up with a decent solution

dberge_at_mx2.redestb.es (nippur) wrote in message news:<ee93444d.0303031009.12e94730_at_posting.google.com>...
> Hi,
>
> I can't seem to come up with an efficient solution for the
> following problem:
>
> BACKGROUND
> ----------
>
> I have data taken from a driving monitor and data from a journey
> log, it consists of information such as vehicle and driver ids, date
> and time of start and end of a trip, and other things. The goal is to
> relate them both, but there are the following complications:
>
> 1.- start/end times in the driving monitor and journey log don't
> match exactly.
>
> 2.- there isn't a one-to-one correspondence between driving monitor
> and journey log records, as the monitor usually resets itself after a
> short period (e.g., a rest stop in the middle of a journey), and then
> starts a new record, but that still comes up as one entry in the
> journey log (we'll ignore the case where one driving monitor record
> comes under two log entries, for simplicity).
>
> so the idea is to match a driving monitor record with the latest
> journey started just before the DM's start time (ignoring for the
> moment the fact that the log timestamp could be later than the
> monitor's)
>
> Graphically, a typical case could be represented a bit like this:
>

>> Here's a sketch of my schema: <<

Some real DDL would have been better, easier to read and testable (if you also gave sample data.

What if we start with a table of log entries, which will have some uniqueness constraints on them. I gather a log entry has only duration

CREATE TABLE Log
(log_nbr INTEGER NOT NULL PRIMARY KEY,
 start_time TIMESTAMP NOT NULL,
 end_time TIMESTAMP NOT NULL,
 CHECK (start_time < end_time),
 UNIQUE (log_nbr, start_time));

The goal seems to be to assign a log number to each monitor report, so let's ad that to the appropriate table:

CREATE TABLE MonitorReports
(vin CHAR(15) NOT NULL,
 odometer DECIMAL (9,1) NOT NULL,
 start_time TIMESTAMP NOT NULL,
 end_time TIMESTAMP, -- null means not finished  CHECK (start_time < end_time),
 log_nbr INTEGER -- null means not assigned

         REFERENCES Log (log_nbr),
 PRIMARY KEY (vin, start_time));

Notice the use of NULLs. Now we can update the MonitorReports with a log number using this:

UPDATE MonitorReports

   SET log_nbr

• (SELECT DISTINCT log_nbr FROM Log AS L1 WHERE MonitorReports.start_time BETWEEN L1.start_time AND L1.end_time OR MonitorReports.end_time BETWEEN L1.start_time AND L1.end_time);

  |--------------------------------------------| |-----|
                      (A)                           (B)  

     (1) (2) (3) (4)  

0) We can see (2) and (3) are clearly part of (A).

1. Can trip (4) be in both log (A) and (B)?
2. Can trip (1) start before (A) and still be counted as part of (A)?
3. Or can a trip belong to two or more log entries?