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: Mladen Gogala <mgogala_at_adelphia.net>
Date: Sat, 01 Nov 2003 07:29:25 -0800
Message-ID: <F001.005D55A1.20031101072925@fatcity.com>


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).
Received on Sat Nov 01 2003 - 09:29:25 CST

Original text of this message

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