Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Finding overlapping time periods - suggestions please

Re: Finding overlapping time periods - suggestions please

From: Stephane Faroult <sfaroult_at_oriole.com>
Date: Sat, 01 Nov 2003 08:59:27 -0800
Message-ID: <F001.005D55A7.20031101085927@fatcity.com>


Mladen,

  If you suggest a convoluted solution like this takes water when you have several million rows I fully agree :-). Funny enough, because it really looks like a purely relational problem, and yet it requires bending backwards. My feeling (and it definitely would deserve time to prove) is that quite possibly it's a design issue - perhaps the proper way would not be to say 'this activitity started then and ended then' but 'at this point in time that activity was running'; in fact, the convoluted part of what I suggest roughly means to do that, changing the design on the fly.

SF

Mladen Gogala wrote:
>
> Stephane, my solution was suggested because the client was a telco which was
> offering each client billing period of their own choosing (weekly, bi-weekly,
> monthly) starting whenever the client wanted. Finding which calls fall in the
> certain period was a major hassle. Of course, the solution like the one that
> I've suggested (and I don't know whether it would really work) would not make
> sense for 3 time periods altogether. What they've ended up implementing was
> a bunch of external procedures based on C and bitmaps, which is, accidentally,
> similar in concept to my solution.
>
> On 2003.11.01 08:09, Stephane Faroult wrote:
> > > babette.turnerunderwood_at_hrdc-drhc.gc.ca wrote:
> > >
> > > I was wondering if anyone had the need to find overlapping time
> > > periods and how to identify them efficiently.
> > >
> > > Here is the scenario:
> > >
> > > Elapsed minutes refer to the actual "clock" time either
> > > spent on a given task. Thus an activity that started at
> > > 9:00 am and finished at 11:00 am on the same day is said to
> > > have 120 elapsed minutes.
> > >
> > > If one task overlaps another (either completely or partially
> > > with another task), then the tasks are said to be
> > > "multitasked". In that case the system will store the
> > > portion of the elapsed time that was multitasked as "elapsed
> > > multitask minutes" and the portion of the time that was not
> > > overlapped as "elapsed single minutes". In addition, for
> > > the portion of time that two or more activities were
> > > simultaneously taking place; their time will be divided by
> > > the number of simultaneous activities and stored as
> > > "prorated multi minutes". The sum of Elapsed Single Minutes
> > > and Prorated Minutes will equal the actual clock time that a
> > > vehicle was active.
> > >
> > > The following example should help to illustrate these
> > > concepts. In the table below a list of fictitious
> > > activities for a vehicle are shown in addition to how the
> > > time is allocated to the various measures:
> > >
> > > Activity Start Time End Time Elapsed Minutes
> > > Elapsed Multitask Minutes Elapsed Single Minutes Prorated Multi
> > > Minutes Prorated Minutes
> > > 1 10:00 12:00 120 60 60 25 85
> > > 3 11:00 13:00 120 120 0 55 55
> > > 4 11:30 13:30 120 90 30 40 70
> > > 7 13:30 16:00 150 0 150 0 150
> > > Totals 510 270 240 120 360
> > > The vehicle was active from 10:00 to 16:00, a total of 6 hours (360
> > > minutes) which is equal to the total of Prorated Minutes.
> > >
> > > The vehicle performed 8 ½ hours (510 minutes) of work during
> > > that 6-hour time span. This can be arrived at by adding the
> > > total of Elapsed Multitask Minutes (270) + the total of
> > > Elapsed Single Minutes (240).
> >
> >
> > Babette,
> >
> > I see the problem as quite similar to the 'let's fill up the
> > calendar' problem. Basically the problem is to have time slices and to
> > know what is going on during those slices.
> >
> > It's pretty easy to build up a view returning one row per minute in the
> > timespan which matters; I am using all_tab_columns as a table with more
> > rows than I need, a smarter solution would be the infinite_dual once
> > suggested by Tim Goraman :
> >
> > select y.t0 + rn / 1440 current_time
> > from (select rownum rn
> > from all_tab_columns
> > where rownum < (select (max(end_time) - min(start_time)) * 1440
> > from activities)) x,
> > (select min(start_time) t0
> > from activities) y
> >
> > If ACTIVITIES (which I supposed to be made of ACTIVITY, START_TIME,
> > END_TIME) is indexed on both START_TIME and END_TIME, it should be fast
> > enough.
> >
> > >From there, it is easy enough to build up a kind of 'bitmap' of
> > activities - this for instance shows a '1' when a given task is active,
> > '0' when it is not :
> >
> > select b.current_time,
> > a.activity,
> > decode(sign(b.current_time - a.start_time),
> > -1, 0,
> > decode(sign(a.end_time - b.current_time), 1, 1,
> > 0))
> > active
> > from activities a,
> > (select y.t0 + rn / 1440 current_time
> > from (select rownum rn
> > from all_tab_columns
> > where rownum <= (select (max(end_time)
> > - min(start_time)) * 1440
> > from activities)) x,
> > (select min(start_time) t0
> > from activities) y) b
> > /
> >
> > a SUM() and a GROUP BY on the current time tell you how many tasks are
> > concurrently active at a given time, etc. Should be enough to get you
> > started ...
> >
> > --
> > Regards,
> >
> > Stephane Faroult
> > Oriole Software
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Stephane Faroult
> > INET: sfaroult_at_oriole.com
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
> --
> Mladen Gogala
> Oracle DBA
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mladen Gogala
> INET: mgogala_at_adelphia.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Sat Nov 01 2003 - 10:59:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US