Seeking help on a complex problem

Seeking help on a complex problem

Hi All,

I have a problem to solve and am seeking the help of
you gurus out there.

There is a table with access information, eg.,

Person Place     Time
====== =====     ====
P1       A       10:01 AM ---> First entry for P1
P1       B       11:00 AM ---> Crossover to B
P1       A       12:15 PM ---> Double xover to A

P2       B       10:10 AM ---> First entry at B for P2
P2       B       11:00 AM ---> Re-entry at B for P2
P2       A       11:15 PM ---> Crossover to A for P2
P2       B       12:11 PM ---> XX over to B

Here are the rules :

If the person goes from A to B it is crossover to B
If the person goes from B to A it is crossover to A
If the person goes from A to B first and then from B to A,
it is double crossover to B
If the person goes from B to A first and then from A to B,
it is double crossover to A

I need to get this information at any given time interval say,
every 15 minutes.

In the above example, the output should be

First
Time Place   Entry  Xover_to_A Xover_to_B XXover_to_A XXover_to_B
==== =====   =====  ========== ========== =========== ===========

10:15  A         1           0          0           0           0
10:15  B         1           0          0           0           0
.
.
11:00  A         0           0          1           0           0
.
.
12:15  A         0           0          0           1           1
.
.
and so on ..

Please let me know if there is any simple of doing this?

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

Seeking help on a complex problem

Sorry for this late answer but i have no time for that SQL puzzle.
Nevertheless, that was a funny one.

Here a single query that generates your result.
It is available only if all times belong to the same day
(time is recorded as a date type).

select
substr(to_char(to_date(to_char(a.time*15*60,'99990'),'SSSSS'),'HH24:MI'),1,5)
"Time",
nvl(f.first_A,0) "first_A", nvl(f.first_B,0) "first_B",
nvl(x.Xover_to_A,0) "Xover_to_A", nvl(x.Xover_to_B,0) "Xover_to_B",
nvl(xx.XXover_to_A,0) "XXover_to_A", nvl(xx.XXover_to_B,0) "XXover_to_B"
from
/* Selects all the known quarters of hour */
(select distinct (trunc((to_number(to_char(time,'SSSSS'))-1)/(15*60))+1) time
from pollachi) a,
/* Counts the first entries in A and B, re-entries are ignore */
(select (trunc((to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1) time,
sum(decode(place,'A',1,0)) first_A,
sum(decode(place,'B',1,0)) first_B
from pollachi a
where not exists (select 1 from pollachi b
where b.person = a.person
and b.time < a.time )
group by (trunc((to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1)) f,
/* Counts the crossovers to A and B, re-entries are ignore */
(select (trunc((to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1) time,
sum(decode(place,'A',1,0)) Xover_to_A,
sum(decode(place,'B',1,0)) Xover_to_B
from pollachi a
where exists ( select 1 from pollachi b
where b.person = a.person
and b.time < a.time
and b.place != a.place
and /* not double crossover */
not exists ( select 1 from pollachi c
where c.person = b.person
and c.time < b.time
and c.place = a.place )
and /* ignore re-entry */
not exists ( select 1 from pollachi d
where d.person = b.person
and d.time between b.time and
a.time-1/86400
and d.place = a.place ) )
group by (trunc((to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1)) x,
/* Counts the double-crossovers, re-entries are ignore */
(select (trunc((to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1) time,
sum(decode(place,'A',1,0)) XXover_to_A,
sum(decode(place,'B',1,0)) XXover_to_B
from pollachi a
where exists ( select 1 from pollachi b
where b.person = a.person
and b.time < a.time
and b.place != a.place
and exists ( select 1 from pollachi c
where c.person = b.person
and c.time < b.time
and c.place = a.place )
and /* ignore re-entry */
not exists ( select 1 from pollachi d
where d.person = b.person
and d.time between b.time and
a.time-1/86400
and d.place = a.place ) )
group by (trunc((to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1)) xx
where f.time (+) = a.time
and x.time (+) = a.time
and xx.time (+) = a.time
order by 1;

With your example, the result is:

Time     first_A    first_B Xover_to_A Xover_to_B XXover_to_A XXover_to_B
----- ---------- ---------- ---------- ---------- ----------- -----------
10:15          1          1          0          0           0           0
11:00          0          0          0          1           0           0
11:15          0          0          1          0           0           0
12:15          0          0          0          0           1           1

I generalize the query for more than 2 places.
More it also counts the re-entries.
This query is available for records on several days but it zeroes the
entry count every day that is an entry in A yesterday followed by an entry
in B today is displayed as two first entries and not an entry in A and a
cross-over to B. I think that's the meaning.

To do this, i change "double-crossover to" to "return to" because you can
crossover several places before returning to a previous place.

Break on date skip 1
select
substr(to_char(to_date(to_char(trunc(a.time*15*60/86400)),'J'),'YYYY/MM/DD'),1,1
0) "Date",

substr(to_char(to_date(to_char(mod(a.time*15*60,86400),'99990'),'SSSSS'),'HH24:M
I'),1,5) "Time",
a.place "Place",
nvl(f.first,0) "First", nvl(re.re_entry,0) "Re-entry",
nvl(x.Xover_to,0) "Xover_to", nvl(xx.Return_to,0) "Return_to"
from
/* Selects all the known quarters of hour */
(select distinct (trunc((to_number(to_char(time,'J'))*86400
+to_number(to_char(time,'SSSSS'))-1)/(15*60))+1) time,
place
from pollachi) a,
/* Counts the first entries in A and B, re-entries are ignore */
(select (trunc((to_number(to_char(time,'J'))*86400
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1) time,
a.place, count(*) first
from pollachi a
where not exists (select 1 from pollachi b
where b.person = a.person
and b.time < a.time
and trunc(b.time) = trunc(a.time) /* same day */ )
group by (trunc((to_number(to_char(time,'J'))*86400
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1),
place) f,
/* Counts the re-entries */
(select (trunc((to_number(to_char(time,'J'))*86400
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1) time,
a.place, count(*) Re_entry
from pollachi a
where exists ( select 1 from pollachi b
where b.person = a.person
and b.time < a.time
and trunc(b.time) = trunc(a.time) /* same day */
and b.place = a.place
and /* not crossover */
not exists ( select 1 from pollachi c
where c.person = b.person
and c.time between b.time and
a.time-1/86400
and c.place != a.place ) )
group by (trunc((to_number(to_char(time,'J'))*86400
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1),
place) re,
/* Counts the crossovers to A and B, re-entries are ignore */
(select (trunc((to_number(to_char(time,'J'))*86400
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1) time,
a.place, count(*) Xover_to
from pollachi a
where exists ( select 1 from pollachi b
where b.person = a.person
and b.time < a.time
and trunc(b.time) = trunc(a.time) /* same day */
and b.place != a.place
and /* not double crossover */
not exists ( select 1 from pollachi c
where c.person = b.person
and c.time < b.time
and trunc(c.time) = trunc(b.time) /*
same day */
and c.place = a.place )
and /* ignore re-entry */
not exists ( select 1 from pollachi d
where d.person = b.person
and d.time between b.time and
a.time-1/86400
and d.place = a.place ) )
group by (trunc((to_number(to_char(time,'J'))*86400
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1),
place) x,
/* Counts the returns, re-entries are ignore */
(select (trunc((to_number(to_char(time,'J'))*86400
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1) time,
a.place, count(*) Return_to
from pollachi a
where exists ( select 1 from pollachi b
where b.person = a.person
and b.time < a.time
and trunc(b.time) = trunc(a.time) /* same day */
and b.place != a.place
and exists ( select 1 from pollachi c
where c.person = b.person
and c.time < b.time
and trunc(c.time) = trunc(b.time) /* same
day */
and c.place = a.place )
and /* ignore re-entry */
not exists ( select 1 from pollachi d
where d.person = b.person
and d.time between b.time and
a.time-1/86400
and d.place = a.place ) )
group by (trunc((to_number(to_char(time,'J'))*86400
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1),
place) xx
where f.time (+) = a.time
and f.place (+) = a.place
and re.time (+) = a.time
and re.place (+) = a.place
and x.time (+) = a.time
and x.place (+) = a.place
and xx.time (+) = a.time
and xx.place (+) = a.place
order by 1, 2;

With the content of the table:

PERSON     PLACE      TIME
---------- ---------- ----------------
P1         A          1999/08/17 10:01
P1         B          1999/08/17 11:00
P1         A          1999/08/17 12:15
P2         B          1999/08/17 10:10
P2         B          1999/08/17 11:00
P2         A          1999/08/17 11:15
P2         B          1999/08/17 12:11
P1         A          1999/08/18 11:01
P1         B          1999/08/18 12:00
P1         A          1999/08/18 13:15
P2         B          1999/08/18 11:10
P2         B          1999/08/18 12:00
P2         A          1999/08/18 12:15
P2         B          1999/08/18 13:11
P1         C          1999/08/18 12:08
P3         C          1999/08/18 12:08
P2         C
...

Hi All,

I have a problem to solve and am seeking the help of
you gurus out there.

There is a table with access information, eg.,

Person Place     Time
====== =====     ====
P1       A       10:01 AM ---> First entry for P1
P1       B       11:00 AM ---> Crossover to B
P1       A       12:15 PM ---> Double xover to A

P2       B       10:10 AM ---> First entry at B for P2
P2       B       11:00 AM ---> Re-entry at B for P2
P2       A       11:15 PM ---> Crossover to A for P2
P2       B       12:11 PM ---> XX over to B

Here are the rules :

If the person goes from A to B it is crossover to B
If the person goes from B to A it is crossover to A
If the person goes from A to B first and then from B to A,
it is double crossover to B
If the person goes from B to A first and then from A to B,
it is double crossover to A

I need to get this information at any given time interval say,
every 15 minutes.

In the above example, the output should be

First
Time Place   Entry  Xover_to_A Xover_to_B XXover_to_A XXover_to_B
==== =====   =====  ========== ========== =========== ===========

10:15  A         1           0          0           0           0
10:15  B         1           0          0           0           0
.
.
11:00  A         0           0          1           0           0
.
.
12:15  A         0           0          0           1           1
.
.
and so on ..

Please let me know if there is any simple of doing this?