Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    12

    Unanswered: Break times cutoff time to be included

    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 day
    create 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 Customer
    create 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 like
    insert 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 NULL
    2 00:15 5 NULL
    3 00:30 5 NULL
    4 00:45 5 NULL
    5 01:00 5 NULL
    6 01:15 5 NULL
    7 01:30 5 NULL
    8 01:45 5 NULL
    9 02:00 5 NULL
    10 02:15 5 NULL
    11 02:30 5 NULL
    12 02:45 5 NULL
    13 03:00 5 NULL
    14 03:15 5 NULL
    15 03:30 5 NULL
    16 03:45 5 NULL
    17 04:00 5 NULL
    18 04:15 5 NULL
    19 04:30 5 NULL
    20 04:45 5 NULL
    21 05:00 5 NULL
    22 05:15 5 NULL
    23 05:30 5 NULL
    24 05:45 5 NULL
    25 06:00 5 Available 06:00--11:00
    26 06:15 5 Available 06:00--11:00
    27 06:30 5 Available 06:00--11:00
    28 06:45 5 Available 06:00--11:00
    29 07:00 5 Available 06:00--11:00
    30 07:15 5 Available 06:00--11:00
    31 07:30 5 Available 06:00--11:00
    32 07:45 5 Available 06:00--11:00
    33 08:00 5 Lunch 08:00--08:30
    34 08:15 5 Lunch 08:00--08:30
    35 08:30 5 Lunch 08:00--08:30
    36 08:45 5 Available 06:00--11:00
    37 09:00 5 Available 06:00--11:00
    38 09:15 5 Available 06:00--11:00
    39 09:30 5 Available 06:00--11:00
    40 09:45 5 Coffee 09:45--10:15
    41 10:00 5 Coffee 09:45--10:15
    42 10:15 5 Coffee 09:45--10:15
    43 10:30 5 Available 06:00--11:00
    44 10:45 5 Available 06:00--11:00
    45 11:00 5 Available 06:00--11:00
    46 11:15 5 NULL
    47 11:30 5 Available 11:30--18:00
    48 11:45 5 Available 11:30--18:00


    Now that to acheive this i coded as :
    -- #tmp_schedule to arrive list in above format
    select time_id ,
    time_interval_24
    into #tmp_schedule
    from ref_time_interval
    alter table #tmp_schedule add time_management varchar(50) null

    -- Based on Duty roster, customer available time to be displayed
    update ts
    set 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 dr
    left 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 updating
    update ts
    set time_management = off_time_desc+' '+char(13)+convert(varchar(5),off_start_time,108)+ '--'+convert(varchar(5),off_end_time,108)
    from #break_time ot
    left 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:00
    31 07:30 5 Available 06:00--08:00
    32 07:45 5 Available 06:00--08:00
    33 08:00 5 Lunch 08:00--08:30
    34 08:15 5 Lunch 08:00--08:30
    35 08:30 5 Lunch 08:00--08:30
    36 08:45 5 Available 08:30--09:45
    37 09:00 5 Available 08:30--09:45
    38 09:15 5 Available 08:30--09:45
    39 09:30 5 Available 08:30--09:45
    40 09:45 5 Coffee 09:45--10:15
    41 10:00 5 Coffee 09:45--10:15
    42 10:15 5 Coffee 09:45--10:15
    43 10:30 5 Available 10:15--11:00
    ..
    </code>
    plz guide or suggest me how to approach this..
    Thanks in advance.

  2. #2
    Join Date
    Feb 2010
    Posts
    19
    That seems like a lot of work to do something that Outlook already does. Could you simplify it by inserting a record whenever a "customer" (by which I assume you mean "employee") starts or stops working? If the last "transaction" for a "customer" is the end of a break, that "customer" is considered available; if the "transaction" is the beginning of a break, the "customer" is unavailable for whatever reason. It takes a little more coding, but it cuts down a good bit on the data. Maybe I don't understand exactly what you're trying to do....

  3. #3
    Join Date
    Sep 2009
    Posts
    12
    thanks for your time. Customer( or employee) in dutyroster will be his loggin and loggout times done on that day. As above given for customer 5, two transactions and 10 one transaction is there. which means in that given transaction of time he will be available. If there is transaction of a break in #break_time table which means he will have break even if it is end of time or start of time. but the next available time should start with break end time and available time should end with previous break start time (if available). hope i am clear in my explination.
    In my code i could just directly insert break times only, but could not reach break end time to next available start time and break start time to prior available end time.

  4. #4
    Join Date
    Feb 2010
    Posts
    19
    Could you expand on what you want to do rather than what you have already done? Do you want to produce a list of "customers" who are available at a given time, or a log of how much time a particular customer has been available on a given day, or a schedule for the customer of when he/she must be available, or something else? A clearer explanation may get you better help.

  5. #5
    Join Date
    Sep 2009
    Posts
    12
    <code>
    1 00:00 5 null...
    ...
    29 07:00 5 NULL
    30 07:15 5 Available 06:00--08:00
    31 07:30 5 Available 06:00--08:00
    32 07:45 5 Available 06:00--08:00
    33 08:00 5 Lunch 08:00--08:30
    34 08:15 5 Lunch 08:00--08:30
    35 08:30 5 Lunch 08:00--08:30
    36 08:45 5 Available 08:30--09:45
    37 09:00 5 Available 08:30--09:45
    38 09:15 5 Available 08:30--09:45
    39 09:30 5 Available 08:30--09:45
    40 09:45 5 Coffee 09:45--10:15
    41 10:00 5 Coffee 09:45--10:15
    42 10:15 5 Coffee 09:45--10:15
    43 10:30 5 Available 10:15--11:00
    ..
    </code>
    this represents the customer available in office so that some another task can assign to him. so we need this sort of output that for all the technicians his day schedule form in-time and his breaktimes ( like cofee/tea breaks) he spend. exact time on the total day. If you could observe my code "update.." statement will give output like from duty roster table start and end time will not be exactly match with break start and end times. this work has to be done.
    In above details, Duty_roster has 6:00 to 11:00 first transaction, and break_time has 8:00 to 8:30, now the output we need to display shoule be included with break times like 6:00 -- 8:00 and 8:30 -- 11:00. this break time to update with dutyroster i could not acheive. plz guide me on this. Thank you very much shiftmore f0r your time .

  6. #6
    Join Date
    Feb 2007
    Posts
    38
    Hi, Try this ( I could not add comments as it's over 10000 chars)

    IF OBJECT_ID('tempdb..#duty_roster') IS NOT NULL
    drop table #duty_roster

    create 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,'2010-02-25 00:00:00','2010-02-25 06:00:00','2010-02-25 18:00:00')
    --insert into #duty_roster values(5,'2010-02-25 00:00:00','2010-02-25 11:30:00','2010-02-25 18:00:00')
    insert into #duty_roster values(10,'2010-02-25 00:00:00','2010-02-25 11:30:00','2010-02-25 18:00:00')
    select * from #duty_roster

    IF OBJECT_ID('tempdb..#break_time') IS NOT NULL
    drop table #break_time

    create 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,'2010-02-25 00:00:00','Lunch','2010-02-25 08:00:00','2010-02-25 08:30:00')
    insert into #break_time values(5,'2010-02-25 00:00:00','Coffee','2010-02-25 09:45:00','2010-02-25 10:15:00')
    insert into #break_time values(5,'2010-02-25 00:00:00','GoneHome','2010-02-25 11:00:00','2010-02-25 11:30:00')
    insert into #break_time values(10,'2010-02-25 00:00:00','StandDown','2010-02-25 12:00:00','2010-02-25 13:30:00')
    insert into #break_time values(10,'2010-02-25 00:00:00','Outbreak','2010-02-25 16:45:00','2010-02-25 17:15:00')

    Select * From #break_time

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].ref_time_interval') AND type in (N'U'))
    DROP TABLE [dbo].ref_time_interval
    create table ref_time_interval
    (
    time_id int identity(1,1),
    time_interval_24 nvarchar(10)
    )

    DECLARE @ID INT
    DECLARE @CMD Varchar(300)
    SET @ID=0
    WHILE @ID < 24
    BEGIN
    SET @CMD = 'insert into ref_time_interval values (''' + RIGHT('0' + Cast(@ID as varchar(20)),2) + ':00'')'
    EXEC (@CMD)
    SET @CMD = 'insert into ref_time_interval values (''' + RIGHT('0' + Cast(@ID as varchar(20)),2) + ':15'')'
    EXEC (@CMD)
    SET @CMD = 'insert into ref_time_interval values (''' + RIGHT('0' + Cast(@ID as varchar(20)),2) + ':30'')'
    EXEC (@CMD)
    SET @CMD = 'insert into ref_time_interval values (''' + RIGHT('0' + Cast(@ID as varchar(20)),2) + ':45'')'
    EXEC (@CMD)
    SET @ID=@ID+1
    END

    IF OBJECT_ID('tempdb..#tmp_schedule') IS NOT NULL
    drop table #tmp_schedule

    select cust_id.Customer_id, time_id ,
    time_interval_24
    into #tmp_schedule
    from ref_time_interval,
    (select distinct Customer_id from #duty_roster) cust_id

    alter table #tmp_schedule add time_management varchar(50) null

    update ts
    set time_management = off_time_desc+' '+char(13)+convert(varchar(5),off_start_time,108)+ '--'+convert(varchar(5),off_end_time,108)
    from #break_time ot
    left join #tmp_schedule ts on ot.Customer_id=ts.Customer_id and ts.time_id
    between
    (DATEDIFF ( minute , Cast(convert(varchar(10),off_start_time,121) as datetime) , off_start_time )/15) + 1
    and
    (DATEDIFF ( minute , Cast(convert(varchar(10),off_end_time,121) as datetime) , off_end_time )/15) + 1
    where ts.time_id
    between
    (DATEDIFF ( minute , Cast(convert(varchar(10),off_start_time,121) as datetime) , off_start_time )/15) + 1
    and
    (DATEDIFF ( minute , Cast(convert(varchar(10),off_end_time,121) as datetime) , off_end_time )/15) + 1
    and ts.Customer_id = ot.Customer_id

    IF OBJECT_ID('tempdb..#final_duty_roster') IS NOT NULL
    drop table #final_duty_roster

    create table #final_duty_roster
    (
    Customer_id int,
    start_id int not null,
    end_id int not null,
    time_management varchar(50) not null
    )

    Declare @Try_no INT
    ,@Customer_id INT
    ,@start_id INT
    , @end_id INT
    , @time_management varchar(50)
    ,@prev_Customer_id INT
    ,@prev_start_id INT
    , @prev_end_id INT
    , @prev_time_management varchar(50)
    DECLARE schedule_cursor CURSOR FOR
    select Customer_id
    , t.time_id [start_id]
    , i.time_id end_id
    , t.time_management
    from #tmp_schedule t
    Left join ref_time_interval i
    On right(t.time_management,5)=i.time_interval_24
    Where (Select count(*)
    from #tmp_schedule
    where time_management=t.time_management and t.Customer_id=customer_id
    and time_id<t.time_id)<1
    and t.time_management is not null
    Order By [Customer_id],[start_id]

    OPEN schedule_cursor
    FETCH NEXT FROM schedule_cursor INTO @Customer_id, @start_id, @end_id, @time_management
    SET @prev_customer_id=-1
    WHILE @@FETCH_STATUS = 0
    BEGIN
    If @prev_customer_id<>@customer_id
    SET @Try_no=0

    If @Try_no=0
    BEGIN
    Insert Into #final_duty_roster
    SELECT @Customer_id [Customer_id]
    ,(SELECT Min(A.[Start_id])
    FROM (
    SELECT
    Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2) [Start_time]
    ,Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2) [End_time]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2)) [Start_id]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2)) [End_id]
    FROM #duty_roster
    where Customer_id=@Customer_id
    ) A
    )

    [start_id]

    , @start_id -1 [end_ID]
    , 'Available ' +

    (SELECT Min(A.[Start_time])
    FROM (
    SELECT
    Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2) [Start_time]
    ,Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2) [End_time]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2)) [Start_id]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2)) [End_id]
    FROM #duty_roster
    where Customer_id=@Customer_id
    ) A
    ) + ':' + time_interval_24 [time_management]
    FROM ref_time_interval
    WHERE time_id=@start_id
    UNION
    SELECT @Customer_id [Customer_id], @start_id [start_id], @end_id [end_ID], @time_management [time_management]
    END
    ELSE
    BEGIN
    Insert Into #final_duty_roster
    SELECT @Customer_id [Customer_id], @prev_end_id+1 [start_id], @start_id -1 [end_ID],
    'Available ' + (Select time_interval_24 From ref_time_interval Where time_id= @prev_end_id)
    + '-' + (Select time_interval_24 From ref_time_interval Where time_id= @start_id) [time_management]
    UNION
    SELECT @Customer_id [Customer_id], @start_id [start_id], @end_id [end_ID], @time_management [time_management]
    END
    SET @Try_no=@Try_no+1
    SELECT @prev_customer_id=@customer_id, @prev_start_id=@start_id, @prev_end_id=@end_id, @prev_time_management=@time_management
    FETCH NEXT FROM schedule_cursor INTO @Customer_id, @start_id, @end_id, @time_management

    END

    Insert Into #final_duty_roster
    SELECT @Customer_id [Customer_id], @prev_end_id+1 [start_id],
    (SELECT Max(A.[End_id])
    FROM (
    SELECT
    Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2) [Start_time]
    ,Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2) [End_time]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2)) [Start_id]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2)) [End_id]
    FROM #duty_roster
    where Customer_id=@Customer_id
    ) A
    ) [end_ID],
    'Available ' + (Select time_interval_24 From ref_time_interval Where time_id= @prev_end_id)
    + '-' +
    (
    SELECT Max(A.[End_time])
    FROM (
    SELECT
    Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2) [Start_time]
    ,Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2) [End_time]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2)) [Start_id]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2)) [End_id]
    FROM #duty_roster
    where Customer_id=@Customer_id
    ) A
    ) [time_management]

    CLOSE schedule_cursor
    DEALLOCATE schedule_cursor

    SELECT a.Customer_id,a.Time_id,a.Time_interval_24,b.time_ management
    FROM #tmp_schedule a
    join #final_duty_roster b
    On a.Customer_id=b.Customer_id and a.time_id between b.start_id and b.end_id

  7. #7
    Join Date
    Feb 2007
    Posts
    38
    Hi

    You need to change the following code in the cursor at last stage.
    From:
    If @prev_customer_id<>@customer_id
    SET @Try_no=0
    To:
    If @prev_customer_id<>@customer_id
    BEGIN
    SET @Try_no=0
    If @prev_customer_id<>-1
    BEGIN
    Insert Into #final_duty_roster
    SELECT @prev_customer_id [Customer_id], @prev_end_id+1 [start_id],
    (SELECT Max(A.[End_id])
    FROM (
    SELECT
    Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2) [Start_time]
    ,Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2) [End_time]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2)) [Start_id]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2)) [End_id]
    FROM #duty_roster
    where Customer_id=@prev_customer_id
    ) A
    ) [end_ID],
    'Available ' + (Select time_interval_24 From ref_time_interval Where time_id= @prev_end_id)
    + '-' +
    (
    SELECT Max(A.[End_time])
    FROM (
    SELECT
    Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2) [Start_time]
    ,Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2) [End_time]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,start_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,start_time) as varchar(2)),2)) [Start_id]
    , (Select time_id From ref_time_interval Where time_interval_24=Right('0' + cast(datepart(hour,end_time) as varchar(2)),2)
    + ':' + Right('0' + cast(datepart(minute,end_time) as varchar(2)),2)) [End_id]
    FROM #duty_roster
    where Customer_id=@prev_customer_id
    ) A
    ) [time_management]

    END
    END


    This will add available time slots after GoneHome for Customer_id=5.

  8. #8
    Join Date
    Sep 2009
    Posts
    12
    Hi terry, thank you very much for your precious time for me.
    This is really very helpful to me. Thanks for stretching your excellent friendly hands towards me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •