SQL Server Performance

available day schedule removing break times

Discussion in 'SQL Server 2008 General Developer Questions' started by pprasanth, Feb 10, 2010.

  1. pprasanth New Member

    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 for the smae day ( 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 at earliest plz
  2. FrankKalis Moderator

    Welcome to the forum!
    Is this a homework question?
  3. pprasanth New Member

    its part of my work assigned. i am not getting the logic how to work in sql 2005.
    Sorry that i overlooked date and typed worng in my earlier 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'.
    Thank you in advance for you kind help.

Share This Page