Break time cutoff times to display

I am working
in sqlserver 2005 <
code>-- This table contains In-time(ie start_time) and Out-time(ie End_time) of customer on that daycreate
table #duty_roster(duty_roster_id
bigint identity(1,1),Customer_id
int,Work_date
datetime not null,start_time
datetime not null,end_time datetime not null
)insert
into #duty_roster values(5,'25-02-2010 00:00:00','25-02-2010 06:00:00','25-02-2010 11:00:00')insert
into #duty_roster values(5,'25-02-2010 00:00:00','25-02-2010 11:30:00','25-02-2010 18:00:00')insert
into #duty_roster values(10,'25-02-2010 00:00:00','25-02-2010 11:30:00','25-02-2010 18:00:00')-- this table contains break times of the Customercreate
table #break_time(off_id bigint identity(1,1), Customer_id
int,work_date
datetime not null,off_time_desc
nvarchar(50) not null,off_start_time
datetime null,off_end_time datetime null,
)insert
into #break_time values(5,'25-02-2010 00:00:00','Lunch','25-02-2010 08:00:00','25-02-2010 08:30:00') insert
into #break_time values(5,'25-02-2010 00:00:00','Coffee','25-02-2010 09:45:00','25-02-2010 10:15:00')insert
into #break_time values(10,'25-02-2010 00:00:00','StandDown','25-02-2010 12:00:00','25-02-2010 13:30:00')insert
into #break_time values(10,'25-02-2010 00:00:00','Outbreak','25-02-2010 16:45:00','25-02-2010 17:15:00')create
table ref_time_interval(time_id
int identity(1,1),time_interval_24 nvarchar(10)
)-- In this table data will be every 15 min interval of time will be stored likeinsert
into ref_time_interval (00:00)insert
into ref_time_interval (00:15)insert
into ref_time_interval (00:30)insert
into ref_time_interval (00:45)insert
into ref_time_interval (01:00)insert into ref_time_interval (01:15)
....
so on ...insert
into ref_time_interval (23:30)insert
into ref_time_interval (23:45)to get the customer in his available time in office,i need to show along with the break times
so
output i need in shown format :Timeid Time_interval customer_id time_management-----------------------------------------------1 00
:00 5 NULL2 00
:15 5 NULL3 00
:30 5 NULL4 00
:45 5 NULL5 01
:00 5 NULL6 01
:15 5 NULL7 01
:30 5 NULL8 01
:45 5 NULL9 02
:00 5 NULL10 02
:15 5 NULL11 02
:30 5 NULL12 02
:45 5 NULL13 03
:00 5 NULL14 03
:15 5 NULL15 03
:30 5 NULL16 03
:45 5 NULL17 04
:00 5 NULL18 04
:15 5 NULL19 04
:30 5 NULL20 04
:45 5 NULL21 05
:00 5 NULL22 05
:15 5 NULL23 05
:30 5 NULL24 05
:45 5 NULL25 06
:00 5 Available 06:00--11:0026 06
:15 5 Available 06:00--11:0027 06
:30 5 Available 06:00--11:0028 06
:45 5 Available 06:00--11:0029 07
:00 5 Available 06:00--11:0030 07
:15 5 Available 06:00--11:0031 07
:30 5 Available 06:00--11:0032 07
:45 5 Available 06:00--11:0033 08
:00 5 Lunch 08:00--08:3034 08
:15 5 Lunch 08:00--08:3035 08
:30 5 Lunch 08:00--08:3036 08
:45 5 Available 06:00--11:0037 09
:00 5 Available 06:00--11:0038 09
:15 5 Available 06:00--11:0039 09
:30 5 Available 06:00--11:0040 09
:45 5 Coffee 09:45--10:1541 10
:00 5 Coffee 09:45--10:1542 10
:15 5 Coffee 09:45--10:1543 10
:30 5 Available 06:00--11:0044 10
:45 5 Available 06:00--11:0045 11
:00 5 Available 06:00--11:0046 11
:15 5 NULL47 11
:30 5 Available 11:30--18:0048 11:45 5 Available 11:30--18:00 Now
that to acheive this i coded as :-- #tmp_schedule to arrive list in above formatselect
time_id ,time_interval_24into
#tmp_schedule from
ref_time_intervalalter
table #tmp_schedule add time_management varchar(50) null-- Based on Duty roster, customer available time to be displayedupdate
tsset
customer_id = ts.Customer_id,time_management
= 'Available'+' '+char(13)+convert(varchar(5),start_time,108)+'--'+convert(varchar(5),end_time,108)from
#duty_roster drleft join #tmp_schedule ts on dr.Customer_id = ts.Customer_id
and ts.time_id between dbo.fn_get_interval_id(start_time) and dbo.fn_get_interval_id(end_time)-- off time break times are been updatingupdate
tsset
time_management = off_time_desc+' '+char(13)+convert(varchar(5),off_start_time,108)+'--'+convert(varchar(5),off_end_time,108)from
#break_time otleft
join #tmp_schedule ts on ts.time_id between dbo.fn_get_interval_id(off_start_time) and dbo.fn_get_interval_id(off_end_time)where ts.time_id between dbo.fn_get_interval_id(off_start_time) and dbo.fn_get_interval_id(off_end_time)
and
ts.Customer_id = ot.Customer_id select time_id, time_interval_24, Customer_id, time_management from #tmp_schedule
This code displays only the exacty
time as per in table only, but upto the break of time should be cutoff and display in shown format.Timeid Time_interval customer_id time_management-----------------------------------------------1 00:00 5 NULL
...30 07
:15 5 Available 06:00--08:0031 07
:30 5 Available 06:00--08:0032 07
:45 5 Available 06:00--08:0033 08
:00 5 Lunch 08:00--08:3034 08
:15 5 Lunch 08:00--08:3035 08
:30 5 Lunch 08:00--08:3036 08
:45 5 Available 08:30--09:4537 09
:00 5 Available 08:30--09:4538 09
:15 5 Available 08:30--09:4539 09
:30 5 Available 08:30--09:4540 09
:45 5 Coffee 09:45--10:1541 10
:00 5 Coffee 09:45--10:1542 10
:15 5 Coffee 09:45--10:1543 10
:30 5 Available 10:15--11:00..
</
code>plz guide
or suggest me how to approach this..Thanks
in advance.