Re: Linking records together
- From: "PJ" <pjv_dev.nospam@xxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 23 Sep 2009 19:36:37 +0200
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" <oratune@xxxxxxx> wrote in message
news:7c8b9226-0a29-47ef-9340-0f40c1c9ca64@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Sep 23, 8:22 am, "PJ" <pjv_dev.nos...@xxxxxxxxxxxxxxxxxxxx> 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
.
- Follow-Ups:
- Re: Linking records together
- From: ddf
- Re: Linking records together
- From: ddf
- Re: Linking records together
- References:
- Linking records together
- From: PJ
- Re: Linking records together
- From: ddf
- Linking records together
- Prev by Date: Re: Linking records together
- Next by Date: Re: COLLECT - collection of collections
- Previous by thread: Re: Linking records together
- Next by thread: Re: Linking records together
- Index(es):
Relevant Pages
|
