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
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.