Seeking help on a complex problem

Seeking help on a complex problem

Post by polla.. » Wed, 14 Jul 1999 04:00:00



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.

 
 
 

Seeking help on a complex problem

Post by Michel Cado » Thu, 19 Aug 1999 04:00:00


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
...

read more »

 
 
 

1. 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?

Thanks in advance

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

2. offline Sync repost - any expertise welcome

3. a problem with seek using complex index

4. DBMS_PIPE Question

5. Problem with complex sysindexes and complex stored procedures

6. Help SQL ADO Output Params

7. Seeking advice on complex query approaches

8. Options for accessing Cognos cube data?

9. A complex problem, please help!

10. Complex join problem with lookup table - need Help

11. Help: Seek command won't seek

12. VB6: Problem sharing ADO connection between COM Objects (complex problem)