SQL Server Performance

Break time cutoff times to display

Discussion in 'SQL Server 2005 General Developer Questions' started by pprasanth, Feb 25, 2010.

  1. pprasanth New Member

    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.
  2. FrankKalis Moderator

    Is this issue still current?

Share This Page