Re: Linking records together
Date: Thu, 24 Sep 2009 18:17:34 +0200
Message-ID: <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_days4 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 Thu Sep 24 2009 - 11:17:34 CDT