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: > > > [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
