Re: Linking records together
From: ddf <oratune_at_msn.com>
Date: Wed, 23 Sep 2009 13:50:28 -0700 (PDT)
Message-ID: <3265e221-bd11-4f00-a741-90a813f2be46_at_p15g2000vbl.googlegroups.com>
On Sep 23, 2:55 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> ddf schreef:
>
>
>
>
>
> > 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_date
> > 2 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_item
> > 5 )
> > 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
>
> This does not work for a sequence of more than 4 events. All solutions
> of this type can only work for a fixed max. number of events.
> Proof:
> insert into event_track values (8,'E','F',to_date('2009-11-27', 'RRRR-MM-DD
> '));
> commit;
>
> and try again.
>
> I think pl/sql is your friend here. Create a function that calculates
> the end-point for a given start point (that one could use a connect by
> or a cursor loop) and use it in your select statement:
> select start_item, f_calc_end_item(startitem) end_item from event_track.
> Only problem remaining is: what are the start items? (I leave that to
> you..., hint: use a not exists clause.
>
> I hope performance will not be an issue ;-)
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 23 Sep 2009 13:50:28 -0700 (PDT)
Message-ID: <3265e221-bd11-4f00-a741-90a813f2be46_at_p15g2000vbl.googlegroups.com>
On Sep 23, 2:55 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> ddf schreef:
>
>
>
>
>
> > 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_date
> > 2 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_item
> > 5 )
> > 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
>
> This does not work for a sequence of more than 4 events. All solutions
> of this type can only work for a fixed max. number of events.
> Proof:
> insert into event_track values (8,'E','F',to_date('2009-11-27', 'RRRR-MM-DD
> '));
> commit;
>
> and try again.
>
> I think pl/sql is your friend here. Create a function that calculates
> the end-point for a given start point (that one could use a connect by
> or a cursor loop) and use it in your select statement:
> select start_item, f_calc_end_item(startitem) end_item from event_track.
> Only problem remaining is: what are the start items? (I leave that to
> you..., hint: use a not exists clause.
>
> I hope performance will not be an issue ;-)
>
> Shakespeare- Hide quoted text -
>
> - Show quoted text -
I never said it was an absolute answer. :D
David Fitzjarrell Received on Wed Sep 23 2009 - 15:50:28 CDT