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.