Re: Linking records together
Date: Fri, 25 Sep 2009 08:56:37 +0200
Message-ID: <4abc69a0$0$9600$703f8584_at_news.kpn.nl>
Very nice! Very clean coding and just what I need. Thanks!
"Michel Cadot" <micadot{at}altern{dot}org> wrote in message
news:4abb9b9e$0$15180$426a74cc_at_news.free.fr...
>
> "PJ" <pjv_dev.nospam_at_geenspam.hotmail.com> a écrit dans le message de
> news: 4aba2123$0$23459$703f8584_at_news.kpn.nl...
> | 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
> |
> |
>
> create table event_track (
> event_id number(10),
> start_item varchar2(10),
> end_item varchar2(10),
> event_date date
> )
> /
> insert all
> into event_track values(1,'A','B',to_date('2009-10-11', 'RRRR-MM-DD'))
> into event_track values(2,'G','H',to_date('2009-10-12', 'RRRR-MM-DD'))
> into event_track values(3,'B','C',to_date('2009-10-13', 'RRRR-MM-DD'))
> into event_track values(4,'H','I',to_date('2009-10-17', 'RRRR-MM-DD'))
> into event_track values(5,'C','D',to_date('2009-10-19', 'RRRR-MM-DD'))
> into event_track values(6,'I','J',to_date('2009-10-22', 'RRRR-MM-DD'))
> into event_track values(7,'J','K',to_date('2009-10-23', 'RRRR-MM-DD'))
> into event_track values(8,'D','E',to_date('2009-10-27', 'RRRR-MM-DD'))
> into event_track values(9,'E','F',to_date('2009-11-27', 'RRRR-MM-DD'))
> select * from dual;
> alter session set nls_date_format='YYYY-MM-DD';
>
> SQL> select * from event_track order by 1;
> EVENT_ID START_ITEM END_ITEM EVENT_DATE
> ---------- ---------- ---------- ----------
> 1 A B 2009-10-11
> 2 G H 2009-10-12
> 3 B C 2009-10-13
> 4 H I 2009-10-17
> 5 C D 2009-10-19
> 6 I J 2009-10-22
> 7 J K 2009-10-23
> 8 D E 2009-10-27
> 9 E F 2009-11-27
>
> 9 rows selected.
>
> SQL> select connect_by_root start_item start_item,
> 2 end_item,
> 3 event_date - connect_by_root event_date evt_days
> 4 from event_track
> 5 where connect_by_isleaf = 1
> 6 connect by prior end_item = start_item
> 7 start with start_item not in (select end_item from event_track)
> 8 order by 1
> 9 /
> START_ITEM END_ITEM EVT_DAYS
> ---------- ---------- ----------
> A F 47
> G K 11
>
> 2 rows selected.
>
> Regards
> Michel
>
>
>
Received on Fri Sep 25 2009 - 01:56:37 CDT