Results 1 to 1 of 1
  1. #1
    Join Date
    May 2009
    Posts
    1

    Unanswered: manage overlapping schedules

    Hi Friends,

    I am faciing a problem with overlapping schedules for internet services. We have the capability of storing the availability time and non-availability time of the internet service.

    Please consider the following scenario:
    SHEDULE 1: (Schedule for the availability time of the internet services)
    start_date: 01-Jan-2009
    end_date: 31-Jan-2009
    start_time: 9.00 a.m.
    end_time: 5.00 p.m

    SHEDULE 2: (Schedule for the non-availability time of the internet services)
    start_date: 15-Jan-2009
    end_date: 20-Jan-2009
    start_time: 1.00 p.m.
    end_time: 2.00 p.m

    SHEDULE 3: (Schedule for the non-availability time of the internet services)
    start_date: 24-Jan-2009
    end_date: 26-Jan-2009
    start_time: 1.00 p.m.
    end_time: 2.00 p.m

    The information would be stored in the database in the following manner

    schedule_id schedule_type start_date end_date start_time end_time
    ----------- ------------- ---------- ---------- -------- ---------
    1 Available 01-Jan-2009 31-Jan-2009 9.00 a.m 5.00 p.m
    2 Non-Available 15-Jan-2009 20-Jan-2009 1.00 p.m 2.00 p.m
    3 Non-Available 24-Jan-2009 26-Jan-2009 1.00 p.m 2.00 p.m


    Now I want to break up the above schedule in actual available minutes depending upon the available/non-available schedule of the internet services for the month of january. Note that schedule 2 and 3 overlap schedule number 1.

    Expected Output
    ---------------
    start_date end_date start_time end_time work_minutes_per_day
    ---------- -------- ---------- -------- --------------------
    01-Jan-2009 14-Jan-2009 9.00 a.m 5.00 p.m 480
    15-Jan-2009 20-Jan-2009 9.00 a.m 1.00 p.m 240
    15-Jan-2009 20-Jan-2009 2.00 a.m 5.00 p.m 180
    15-Jan-2009 20-Jan-2009 1.00 p.m 2.00 p.m -60
    21-Jan-2009 23-Jan-2009 9.00 a.m 5.00 p.m 480
    24-Jan-2009 26-Jan-2009 9.00 a.m 1.00 p.m 240
    24-Jan-2009 26-Jan-2009 2.00 a.m 5.00 p.m 180
    24-Jan-2009 26-Jan-2009 1.00 p.m 2.00 p.m -60
    27-Jan-2009 31-Jan-2009 9.00 a.m 5.00 p.m 480

    Please have a look at the attachment: output.txt if the original and expected output are not clearly visible/formated.

    Please note how the schedules for records between 15-Jan-2009 and 20-Jan-2009 have been broken into multiple records depending upon the non-availability time of the internet services. The same concept have been applied for the records between 24-Jan-2009 and 26-Jan-2009.

    The above expected result is for display/reporting purpose. Hence cannot tweak around with the requirements.

    Any help in terms of query or algorithms would be highly appreciated.

    Thanks in advance.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •