Re: Linking records together

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 23 Sep 2009 21:55:02 +0200
Message-ID: <4aba7d19$0$83241$e4fe514c_at_news.xs4all.nl>

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 Received on Wed Sep 23 2009 - 14:55:02 CDT