Re: Linking records together
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 23 Sep 2009 23:46:34 +0200
Message-ID: <4aba973d$0$83238$e4fe514c_at_news.xs4all.nl>
Shakespeare schreef:
>>> 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 -
BEGIN
OPEN c_nextpoint(p_startpoint);
FETCH c_nextpoint
INTO r_nextpoint;
IF c_nextpoint%NOTFOUND THEN
-- this could be done by a connect by and taking the last record -- as well, but I love recursion ....
END; SELECT t3.event_id, t3.start_item, t4.end_item
WHERE t4.event_id = t3.end_event
ORDER BY 1;
Date: Wed, 23 Sep 2009 23:46:34 +0200
Message-ID: <4aba973d$0$83238$e4fe514c_at_news.xs4all.nl>
Shakespeare schreef:
> ddf schreef: >> 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 > > I just responded to your challenge: > "> Test it on your data to see if the query returns correct results." > > ;-) > > Shakespeare
I just could not leave it alone:
CREATE OR REPLACE FUNCTION f_endpoint(p_startpoint IN NUMBER) RETURN
NUMBER IS
CURSOR c_nextpoint(b_startpoint NUMBER) IS
SELECT t2.event_id
FROM event_track t1, event_track t2
WHERE t1.event_id = b_startpoint
AND t1.end_item = t2.start_item;
r_nextpoint c_nextpoint%ROWTYPE;
BEGIN
OPEN c_nextpoint(p_startpoint);
FETCH c_nextpoint
INTO r_nextpoint;
IF c_nextpoint%NOTFOUND THEN
CLOSE c_nextpoint;
RETURN p_startpoint;
ELSE
CLOSE c_nextpoint;
RETURN f_endpoint(r_nextpoint.event_id);
END IF;
-- this could be done by a connect by and taking the last record -- as well, but I love recursion ....
END; SELECT t3.event_id, t3.start_item, t4.end_item
FROM (SELECT t.event_id, t.start_item, f_endpoint(t.event_id) end_event
FROM event_track t
WHERE NOT EXISTS
(SELECT 1 FROM event_track t2 WHERE t2.end_item =
t.start_item)) t3,
event_track t4
WHERE t4.event_id = t3.end_event
ORDER BY 1;
EVENT_ID START_ITEM END_ITEM 1 A E 2 G K
Not the most optimal version I'm sure, but it works. Tested with more than 4 steps as well.
Shakespeare Received on Wed Sep 23 2009 - 16:46:34 CDT