Need some help... 2

[RedInTheCorner]

Hello everyone,
I have little bit problem with a query. Query's aim is getting max number of intersections of processes. My table is
PROCESS_TEST
Process StartDate EndDate
1 01/02/2006 02/03/2006
2 04/02/2006 07/03/2006
3 05/02/2006 08/0372006
4 03/01/2006 27/01/2006

for above sample max number of intercestions is 3
because in the time range 05/02/2006 to 02/03/2006 I have 1, 2 and 3 process alive

my solution is
----
you may need following:
CREATE TABLE PROCESS_TEST
(Process NUMBER(4) PRIMARY KEY,
StartDate DATE,
EndDate DATE);

INSERT INTO PROCESS_TEST VALUES (1, TO_DATE('01/02/2006', 'dd/mm/yyyy'), TO_DATE('02/03/2006', 'dd/mm/yyyy'));

INSERT INTO PROCESS_TEST VALUES (2, TO_DATE('04/02/2006', 'dd/mm/yyyy'), TO_DATE('07/03/2006', 'dd/mm/yyyy'));

INSERT INTO PROCESS_TEST VALUES (3, TO_DATE('05/02/2006', 'dd/mm/yyyy'), TO_DATE('08/03/2006', 'dd/mm/yyyy'));
INSERT INTO PROCESS_TEST VALUES (4, TO_DATE('03/01/2006', 'dd/mm/yyyy'), TO_DATE('27/01/2006', 'dd/mm/yyyy'));
----
SELECT MAX(X) FROM
(
SELECT COUNT(*) X
FROM PROCESS_TEST P1, PROCESS_TEST P2
WHERE P1.EndDate > P2.StartDate
aND P2.EndDate > P1.StartDate
GROUP BY P1.Process
);

it works cool but it would be costly when we have more process I cannot imagine how it would be if I have 1 million process....
Do you have any other idea?

with my best wishes

Atilla

 

[Dagon]

Looks quite reasonable. I tried to do it using ranking functions, but the best I've been able to do so far is this.

select curdate, proc_count from
(select curdate, proc_count, rank() over (order by proc_count desc) as rnk
from
(select curdate, sum(proc_increment) over (order by curdate rows between unbounded preceding and current row) proc_count
from
(select process, startdate as curdate, 1 as proc_increment
from process_test
union all
select process, enddate as curdate, -1 as proc_increment
from process_test)))
where rnk = 1

 

[RedInTheCorner]

thanks it is a good solution but it is little bit advanced for me

I usually like simple queries... It is working perfect but little bit hard to memorize... Anyway still I am on behind of my self-join query till I find a better one or learn Oracle's Analytic functions

 

[RedInTheCorner]

O

http://www My

Query is not working rite I little bit simplifed your query and use it, it is really great and genious
Thanks for your help....

 

[KenCunningham]

Does that mean Dagon's solution meets your needs? If so, why not award a star in recognition?

I want to be good, is that not enough?

 

[RedInTheCorner]

Sory Dagon I really didn't know awarding issue I am a newbie in this forum and I don't like reading forum rules and features. And Kencunningham thank you too, I give you a star too

 

[KenCunningham]

Too kind!!

I want to be good, is that not enough?