Re: Linking records together

From: ddf <oratune_at_msn.com>
Date: Wed, 23 Sep 2009 07:27:16 -0700 (PDT)
Message-ID: <7c8b9226-0a29-47ef-9340-0f40c1c9ca64_at_g6g2000vbr.googlegroups.com>

On Sep 23, 8:22 am, "PJ" <pjv_dev.nos..._at_geenspam.hotmail.com> wrote:
> Hi all,
>
> We need to track work items across several existing applications. Items can
> start as a document on disk, can be renamed or moved, converted to a task
> in an application, until they reach a certain state. The applications know
> nothing about preceding or following applications and there is no common
> identifier for the items, which has always been a good thing.
>
> My idea is to create a table for all events. Each application will record
> their
> own events. A minimum is one event per application with a starting and
> finishing situation for that application. Example: "file \\folder1\x.doc was
> moved to \\folder2\y.doc". Another application may follow up with
> "\\folder2\y.doc was archived with id 12345". Each event will have a date
> and time.
>
> My table will probably have the following columns:
>
> event_id number(10)
> start_item varchar2(255)
> end_item varchar2(255)
> event_date date
>
> Sample data:
>
> 1    A    B    2009-10-11
> 2    G    H    2009-10-12
> 3    B    C    2009-10-13
> 4    H    I    2009-10-17
>
> Records can be linked from end_item to start_item (the next application in
> line will continue where the previous application stops). So in this sample
> data I have two sets of records (1 with 3, 2 with 4) and recognize two
> starting points A and G with two end points C and I. From A to C takes
> two days, from G to I takes 5 days.
>
> Data entry is no problem. Reporting is. My resultset (a view, another table)
> should be this:
>
> A    C    2
> G    I    5
>
> How can I achieve this? I have looked at the connect by, but I get to many
> records. So basically I have no idea where to start.
>
> Thanks in advance,
> PJ

You're making this too difficult:

SQL> create table event_track(

  2          event_id number(10),
  3          start_item varchar2(255),
  4          end_item varchar2(255),
  5          event_date date

  6 );

Table created.

SQL>
SQL>
SQL> insert all

  2 into event_track
  3 values(1,'A','B',to_date('2009-10-11', 'RRRR-MM-DD'))   4 into event_track
  5 values(2,'G','H',to_date('2009-10-12', 'RRRR-MM-DD'))   6 into event_track
  7 values(3,'B','C',to_date('2009-10-13', 'RRRR-MM-DD'))   8 into event_track
  9 values(4,'H','I',to_date('2009-10-17', 'RRRR-MM-DD'))  10 select * from dual;

4 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> column start_item format a10
SQL> column end_item format a10
SQL>
SQL> select e1.event_id, e1.start_item, e2.end_item, round

(e2.event_date - e1.event_date, 0) event_days   2 from event_track e1, event_track e2   3 where e2.start_item = e1.end_item;

  EVENT_ID START_ITEM END_ITEM EVENT_DAYS ---------- ---------- ---------- ----------

         1 A          C                   2
         2 G          I                   5

SQL> David Fitzjarrell Received on Wed Sep 23 2009 - 09:27:16 CDT