Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    12

    Unanswered: day schedule removing break times

    create table day_break
    (
    id bigint identity(1,1),
    break_start_time datetime ,
    break_end_time datetime
    )

    insert into day_break values('09-03-2010 12:30:00','09-03-2010 13:00:00')
    insert into day_break values('09-03-2010 17:30:00','09-03-2010 19:00:00')
    insert into day_break values('09-04-2010 13:00:00','09-04-2010 16:00:00')


    create table appointment
    (
    appointment_id bigint identity(1,1),
    Work_date datetime not null,
    Start_time datetime not null,
    end_time datetime not null,
    )

    insert into appointment values('09-03-2010 00:00:00','09-03-2010 11:30:00','09-03-2010 13:00:00')
    insert into appointment values('09-03-2010 00:00:00','09-03-2010 14:00:00','09-03-2010 17:30:00')
    insert into appointment values('09-03-2010 00:00:00','09-03-2010 18:00:00','09-03-2010 20:30:00')

    output:
    On given date from day start time to day end time, we need to remove break timings ( from day_break table)and return available timing. output as shown below.

    '09-03-2010 11:30:00','09-03-2010 13:00:00'
    '09-03-2010 14:00:00','09-03-2010 17:30:00'
    '09-03-2010 19:00:00','09-03-2010 20:30:00'

    expecting your friendlyhands ..

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not enough data I'm afraid.

    Can you explain why the first appointment is unaffected?
    What would happen to these appointments?
    Code:
    insert into appointment values('09-03-2010 00:00:00','09-03-2010 18:00:00','09-03-2010 18:30:00')
    insert into appointment values('09-03-2010 00:00:00','09-03-2010 14:30:00','09-03-2010 18:00:00')
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2009
    Posts
    12
    Sorry that i overlooked date in my post.For the given date i.e. on 09-03-2010, as break has only two records, so in appoinment table day schedule is as 11:30 - 13:00, 14:00 - 17.30 and 18:00 - 20:30.
    On same date break schedules as 12:30 - 13:00 and 17:30 - 19:00
    as break falls in first slot so 11:30 - 12:30.
    for second slot is not effected with any breaks
    and finally 3rd slot break end at 19:00, so third slot will be 19:00 to 20:30. So output will be :
    '09-03-2010 11:30:00','09-03-2010 12:30:00'
    '09-03-2010 14:00:00','09-03-2010 17:30:00'
    '09-03-2010 19:00:00','09-03-2010 20:30:00'
    Simple is that for appointment time of a day, we need to omit break times and reallocate the appointment schedule times. Hope i am clear in my explination.
    Example 2:
    if i truncate and insert only one record i.e.
    insert into #day_break values('09-03-2010 10:30:00','09-03-2010 18:00:00')
    then the result should be only

    '09-03-2010 18:00:00','09-03-2010 20:30:00'.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    We are nearly there I think defining the problem. Please run this and check it is correct. Row 5 I need clarification on.
    Code:
    DROP TABLE day_break
    DROP TABLE appointment
    
    create table day_break
    (
    id bigint identity(1,1),
    break_start_time datetime ,
    break_end_time datetime
    )
    
    insert into day_break values('09-03-2010 12:30:00','09-03-2010 13:00:00')
    insert into day_break values('09-03-2010 17:30:00','09-03-2010 19:00:00')
    
    
    create table appointment
    (
    appointment_id bigint identity(1,1),
    Work_date datetime not null,
    Start_time datetime not null,
    end_time datetime not null,
    )
    
    insert into appointment values('09-03-2010 00:00:00','09-03-2010 11:30:00','09-03-2010 13:00:00')
    insert into appointment values('09-03-2010 00:00:00','09-03-2010 14:00:00','09-03-2010 17:30:00')
    insert into appointment values('09-03-2010 00:00:00','09-03-2010 18:00:00','09-03-2010 20:30:00')
    insert into appointment values('09-03-2010 00:00:00','09-03-2010 17:30:00','09-03-2010 18:00:00')
    insert into appointment values('09-03-2010 00:00:00','09-03-2010 12:00:00','09-03-2010 14:00:00')
    
    SELECT  1, '09-03-2010 11:30:00','09-03-2010 12:30:00', 'End time is truncated as it falls in break time'
    UNION ALL 
    SELECT  2, '09-03-2010 14:00:00','09-03-2010 17:30:00', 'No truncation'
    UNION ALL 
    SELECT  3, '09-03-2010 19:00:00','09-03-2010 20:30:00', 'Start time is truncated as it falls in break time'
    UNION ALL 
    SELECT  4, '09-03-2010 00:00:00','09-03-2010 00:00:00', 'This row should not even appear since the whole appointment is a break'
    UNION ALL 
    SELECT  5, '09-03-2010 00:00:00','09-03-2010 00:00:00', 'I DO NOT KNOW WHAT HAPPENS TO THIS APPOINTMENT'
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - please could you use code tags? It puts the code in fixed width font which makes it easier to read. The easier it is to read, the easier for someone to help you
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This was where I got to yesterday. Can be improved, but you've shot off and I still don't know what to do if a break "splits" an appointment.

    Code:
    --Possible answer
    SELECT  appointment_id
          , Start_time          = COALESCE(new_start_time,  start_time)
          , end_time            = COALESCE(new_end_time,    end_time)
    FROM    dbo.appointment
    OUTER APPLY
            (
                SELECT  new_end_time        = break_start_time
                FROM    dbo.day_break
                WHERE   day_break.break_start_time  > appointment.Start_time
                    AND day_break.break_start_time  < appointment.end_time
             ) AS end_times
    OUTER APPLY
            (
                SELECT  new_start_time      = break_end_time
                FROM    dbo.day_break
                WHERE   day_break.break_end_time    > appointment.Start_time
                    AND day_break.break_end_time    < appointment.end_time
             ) AS start_times
    WHERE   NOT EXISTS (SELECT  NULL
                        FROM    dbo.day_break
                        WHERE   break_end_time      >= end_time
                            AND break_start_time    <= Start_time)
        AND appointment_id  IN(1, 2, 3, 4)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2009
    Posts
    12

    Thank you very much

    Thank you for your time.. its appriciable

Posting Permissions

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