Calculate dates

Transact-SQL
1

I need help with this because I'm stuck. Can someone help me to do this procedure in a smart and easy way?

We have a table where we store timestamps on employees when it starts and ends the workday. Now we need to create a procedure that returns three (over_time_1, over_time_2, over_time_3) overtime calculations in hours. Parameter in to the procedure will be start date, end date, and employee number from the tblEmployeeTimestamp table. Start date and end date can be on different days if the employee work at night etc.
The procedure's calculation rules should be as below.

To know if it's a public holiday and "an extra day off" it's defined in a table tblSchema containing employee number, type and date.

over_time_1:      
Monday - Friday        06:00:00 - 06:40:00  //IF employee has number 100-999 and 2000-2999
Monday - Friday        06:00:00 - 06:50:00  //IF employee has number 1000-1999
Monday - Friday        16:00:00 - 20:00:00  
Friday                 13:00:00 - 16:00:00  
OT2                    00:00:00 - 23:59:59  //IF employee has number 100-999 and 1000-1999

over_time_2:
Monday evening - Friday morning       20:00:00 – 06:00:00

over_time_3:                        
Friday evening – Monday morning         20:00:00 - 06.00:00
OT1                                     00:00:00 - 23:59:59


        Examples:

        tblEmployeeTimestamp

        Employee---Startdate-------------Endate-------------
        ----------------------------------------------------
        250--------2015-10-05 06:40:00---2015-10-05 13:00:00

        tblSchema

        Employee---Date----------Type---
        --------------------------------
        250--------2015-12-24----OT1 //Christmas. Public holiday
        250--------2015-12-27----OT2 //Working day between holidays


        OT1 = Public holiday. (Not weekend)
        OT2 = An extra day off, taken to add a weekend to a public holiday. Working day between holidays
2

It may not satisfy all your requirement. but this should give you a head start to your query

note that, to simplify the query logic, i added a overtime table that store the combination of the over time type, weekday, employee type (number 100-999, 1000-1999). You could use complex CASE where statement to achieve the same but that will make the query complex.

declare @tblemployeeTimestamp table
(
    employee    int,
    start_date    datetime,
    end_date    datetime
)

insert into @tblemployeeTimestamp select 250, '2015-10-05 06:30', '2015-10-05 17:00'

declare @tblSchema table
(
    employee    int,
    [Date]        date,
    Type        varchar(3)
)

insert into @tblSchema select 250, '2015-12-24', 'OT1'
insert into @tblSchema select 250, '2015-12-27', 'OT2'

-- New Table to store the overtime by type, week day and emp type
declare    @overtime table
(
    ot_type        varchar(3),
    [weekday]    varchar(3),
    emp_type    int,
    start_time    time,
    end_time    time
)

-- Monday - Friday        06:00:00 - 06:40:00  //IF employee has number 100-999 and 2000-2999
insert into @overtime select 'OT1', 'Mon', 1, '06:00', '06:40'
insert into @overtime select 'OT1', 'Tue', 1, '06:00', '06:40'
insert into @overtime select 'OT1', 'Wed', 1, '06:00', '06:40'
insert into @overtime select 'OT1', 'Thu', 1, '06:00', '06:40'
insert into @overtime select 'OT1', 'Fri', 1, '06:00', '06:40'
-- Monday - Friday        06:00:00 - 06:50:00  //IF employee has number 1000-1999
insert into @overtime select 'OT1', 'Mon', 2, '06:00', '06:50'
insert into @overtime select 'OT1', 'Tue', 2, '06:00', '06:50'
insert into @overtime select 'OT1', 'Wed', 2, '06:00', '06:50'
insert into @overtime select 'OT1', 'Thu', 2, '06:00', '06:50'
insert into @overtime select 'OT1', 'Fri', 2, '06:00', '06:50'
-- Monday - Friday        16:00:00 - 20:00:00
insert into @overtime select 'OT1', 'Mon', 1, '16:00', '20:00'
insert into @overtime select 'OT1', 'Tue', 1, '16:00', '20:00'
insert into @overtime select 'OT1', 'Wed', 1, '16:00', '20:00'
insert into @overtime select 'OT1', 'Thu', 1, '16:00', '20:00'
insert into @overtime select 'OT1', 'Fri', 1, '16:00', '20:00'

declare    @start_date    datetime,
    @end_date    datetime,
    @employee    int

; with
emp_timestamp as
(
    select    employee, 
        emp_type     = case     when     employee between  100 and  999
                    or    employee between 2000 and 2999
                    then     1
                    else     2
                    end,
        start_date, end_date, 
        weekday        = left(datename(weekday, start_date), 3),
        start_time     = convert(time, start_date),
        end_time     = convert(time, end_date)
    from    @tblemployeeTimestamp
)
select    t.employee, t.start_date, t.end_date, ot.ot_type, ot_minutes = sum(ot.ot_minutes)
from    emp_timestamp t
    outer apply
    (
        select    x.ot_type,
            ot_minutes = case when     t.start_time    between    x.start_time and x.end_time
                    then    datediff(minute, t.start_time, x.end_time)
                    when     t.end_time    between    x.start_time and x.end_time
                    then     datediff(minute, x.start_time, t.end_time)
                    else    0
                    end
        from    @overtime x
        where    x.[weekday]    = t.[weekday]
        and    x.emp_type    = t.emp_type
        and    (
                t.start_time     between    x.start_time and x.end_time
            or    t.end_time    between x.start_time and x.end_time
            )
    ) ot
group by t.employee, t.start_date, t.end_date, ot.ot_type
3

Thanks khtan.

I'll try this. I had planned a procedure for this calculation. Do you think it's possible also?

4

yes. it is possible

5

Thanks for your answer. Do you have possibility to show me a procedure for this?

6 
create procedure as your_procedure
as
begin
   -- the query here
end