| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: 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
sum(decode(place,'A',1,0)) first_A,
sum(decode(place,'B',1,0)) first_B
from pollachi a
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 */
sum(decode(place,'A',1,0)) Xover_to_A,
sum(decode(place,'B',1,0)) Xover_to_B
from pollachi a
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 */
sum(decode(place,'A',1,0)) XXover_to_A,
sum(decode(place,'B',1,0)) XXover_to_B
from pollachi a
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
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
+to_number(to_char(time,'SSSSS'))-1)/(15*60))+1) time,
place
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1) time,
a.place, count(*) first
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,
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1) time,
a.place, count(*) Re_entry
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,
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1) time,
a.place, count(*) Xover_to
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,
+to_number(to_char(a.time,'SSSSS'))-1)/(15*60))+1) time,
a.place, count(*) Return_to
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
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 1999/08/18 12:08 P2 C 1999/08/18 14:16
this query gives you:
Date Time Place First Re-entry Xover_to Return_to ---------- ----- ---------- ---------- ---------- ---------- ----------
1999/08/17 10:15 A 1 0 0 0
10:15 B 1 0 0 0
11:00 B 0 1 1 0
11:15 A 0 0 1 0
12:15 A 0 0 0 1
12:15 B 0 0 0 1
1999/08/18 11:15 A 1 0 0 0
11:15 B 1 0 0 0
12:00 B 0 1 1 0
12:15 A 0 0 1 0
12:15 C 1 0 2 0
13:15 A 0 0 0 1
13:15 B 0 0 0 1
14:30 C 0 0 0 1
pollachi_at_my-deja.com a écrit dans le message <7me82i$60s$1_at_nnrp1.deja.com>...
>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? > >Thanks in advance > > > >Sent via Deja.com http://www.deja.com/ >Share what you know. Learn what you don't.Received on Wed Aug 18 1999 - 05:18:08 CDT
 |
 |