Re: Linking records together
From: ddf <oratune_at_msn.com>
Date: Wed, 23 Sep 2009 11:58:59 -0700 (PDT)
Message-ID: <c74b067b-b52a-4c54-8455-619d3f595355_at_m11g2000vbl.googlegroups.com>
On Sep 23, 12:36 pm, "PJ" <pjv_dev.nos..._at_geenspam.hotmail.com> wrote:
> I should have known, the sample data is too easy. It won't work with more
> than two records per item. Real world will have a varying number of records
> per item, probably with an average of 6.
>
> PJ
>
> "ddf" <orat..._at_msn.com> wrote in message
>
> news: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- Hide quoted text -
>
> - Show quoted text -
6 );
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 into event_track
11 values(5,'C','D',to_date('2009-10-19', 'RRRR-MM-DD')) 12 into event_track
13 values(6,'I','J',to_date('2009-10-22', 'RRRR-MM-DD')) 14 into event_track
15 values(7,'J','K',to_date('2009-10-23', 'RRRR-MM-DD')) 16 into event_track
17 values(8,'D','E',to_date('2009-10-27', 'RRRR-MM-DD')) 18 select * from dual;
6 select e11.event_id, e11.start_item, e21.end_item, round (e21.edate2 - e11.edate1,0) evt_days
7 from etrack e11, etrack e21
8 where e21.start_item = e11.end_item 9 order by 1;
Date: Wed, 23 Sep 2009 11:58:59 -0700 (PDT)
Message-ID: <c74b067b-b52a-4c54-8455-619d3f595355_at_m11g2000vbl.googlegroups.com>
On Sep 23, 12:36 pm, "PJ" <pjv_dev.nos..._at_geenspam.hotmail.com> wrote:
> I should have known, the sample data is too easy. It won't work with more
> than two records per item. Real world will have a varying number of records
> per item, probably with an average of 6.
>
> PJ
>
> "ddf" <orat..._at_msn.com> wrote in message
>
> news: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- Hide quoted text -
>
> - Show quoted text -
I gave this a try and it appears to be working:
SQL> -- SQL> -- Create table SQL> -- 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> -- Load data 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 into event_track
11 values(5,'C','D',to_date('2009-10-19', 'RRRR-MM-DD')) 12 into event_track
13 values(6,'I','J',to_date('2009-10-22', 'RRRR-MM-DD')) 14 into event_track
15 values(7,'J','K',to_date('2009-10-23', 'RRRR-MM-DD')) 16 into event_track
17 values(8,'D','E',to_date('2009-10-27', 'RRRR-MM-DD')) 18 select * from dual;
8 rows created.
SQL>
SQL> commit;
Commit complete.
SQL> SQL> column start_item format a10 SQL> column end_item format a10 SQL> SQL> -- SQL> -- Display source data SQL> -- SQL> select event_id, start_item, end_item ,event_date2 from event_track;
EVENT_ID START_ITEM END_ITEM EVENT_DATE
---------- ---------- ---------- --------------------
1 A B 11-OCT-2009 00:00:00
2 G H 12-OCT-2009 00:00:00
3 B C 13-OCT-2009 00:00:00
4 H I 17-OCT-2009 00:00:00
5 C D 19-OCT-2009 00:00:00
6 I J 22-OCT-2009 00:00:00
7 J K 23-OCT-2009 00:00:00
8 D E 27-OCT-2009 00:00:00
8 rows selected.
SQL> SQL> -- SQL> -- Generate desired report SQL> -- SQL> with etrack as( 2 select e1.event_id, e1.start_item, e2.end_item, e2.event_date edate2, e1.event_date edate1 3 from event_track e1, event_track e2 4 where e2.start_item = e1.end_item5 )
6 select e11.event_id, e11.start_item, e21.end_item, round (e21.edate2 - e11.edate1,0) evt_days
7 from etrack e11, etrack e21
8 where e21.start_item = e11.end_item 9 order by 1;
EVENT_ID START_ITEM END_ITEM EVT_DAYS ---------- ---------- ---------- ----------
1 A E 16
2 G K 11
SQL> Test it on your data to see if the query returns correct results.
David Fitzjarrell Received on Wed Sep 23 2009 - 13:58:59 CDT