Calculate dates

[former_SQLA_member1694869]

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. 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 - Friday        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

[MCMartin]

Do you have any specific question related to this task, otherwise it seems to me that we schould do your homework...

[former_SQLA_member1694869]

Martin, this is a problem that we have in our job. We need to find out what type of overtime that the employee worked, for among other things, to know what cost we have on our projects. Even to know what margin we have. To avoid calculate this manually, as today, which requires many hours, so I'll try to solve it via a procedure that I described below. But I need help... We use version 12 of SQL Anyware.

[Vlad]

Well, I have honestly re-read your example around 4-5 times within past 2 days, but I can't still understand how it should work. Could you please give us at least what will happen if you want to check the 250th employee? I see that his overtime is on the 5th of October. Okey, fine. But what else? What does OT1 and OT2 mean?

If you can prepare some sample data (like 5 records) and what you expect to get from them, at least I will fulfill my curiosity 🙂

[former_SQLA_member1694869]

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

What I mean is if employee work days wich are defined in table tblSchema

The table contains a date based on a factory calendar where each day for each employee is typed with a code. The only types that are interesting in this case is OT1 and OT2. The table is just the basis to know if the date is OT1 or OT2 type.

Ex. of timestamps in emplyee table:

    Employee---Startdate-------------Endate-------------
    ----------------------------------------------------
    250--------2015-10-05 06:40:00---2015-10-05 13:00:00
    250--------2015-10-06 06:31:00---2015-10-06 13:00:00
    250--------2015-10-09 05:35:00---2015-10-09 21:30:00
    1100-------2015-10-09 06:38:00---2015-10-07 13:00:00

The procedure should return this:

    Employee---Startdate-------------Endate----------------over_time_1--over_time_2--over_time_3
    250--------2015-10-05 06:40:00---2015-10-05 13:00:00--------0------------0------------0-----
    250--------2015-10-06 06:31:00---2015-10-06 13:00:00--------0,15---------0------------0-----
    250--------2015-10-09 05:35:00---2015-10-09 21:30:00--------7,67---------0,42---------1,5---
    1100-------2015-10-09 06:38:00---2015-10-07 13:00:00--------7,2----------0------------17----

[former_SQLA_member1694869]

Have I explained clearly enough or it is still unclear what I mean?

[Former Member]

Have you tried just coding your rules in SQL as a start? For example, here is some pseudo-code for the first over time calculation, according to my reading of your rules. It could be easily fixed and translated to SQL:

create procedure get_overtime( emp_id, start_date, end_date )
BEGIN
--overtime1
dayofweek = select DOW(start_date)

if start_date is an extra day off and emp_id between 100 and 1999 then
    OT1 = datediff(minute, end_date, start_date)

else if dayofweek between monday and friday then
    if start_date >= 16:00:00 then
        OT1 = max(0, datediff(minute, '20:00:00', start_date) ) 
    end if

    if emp_id between 1000 and 1999 then
        if start_date >= 6:00:00 then
            OT1 += max(0, datediff(minute, min('6:40:00', end_date), start_date) ) 
        endif
    else if emp_id between 100 and 999 or emp_id between 2000 and 2999 then
        if start_date >= 6:00:00 then
            OT1 += max(0, datediff(minute, min('6:50:00', end_date), start_date) ) 
        endif
    endif
    if dayofweek = friday then
        if start_date > 13:00:00 then
            OT1 += max(0, datediff(minute, min('16:00:00', end_date), start_date) ) 
        endif
    endif
endif

...

[former_SQLA_member1694869]

Thanks Jason,

Your principle (your beginning) is exactly what I need help with. It is the logic of how to build up the procedure in the smartest way, I can't handle. And how do I get the procedure to return those three overtime intervals. One question is how to handle dates in table tblSchema. I want to know which dates are of the type OT1 and OT2.

OT1 = Public holiday. (Note weekend).   OT2 = An extra day off.

There are other types in tblSchema too, but that's just the dates that have these types that should be handled in the procedure. I would be extremely grateful if I get help with the whole logic to build this procedure.

[VolkerBarth]

@Rolle: Please help others by stating whether Jason has understood your logic correctly or not...

[former_SQLA_member1694869]

Jason has understood it correct. I just wish to complete it for the other intervals...

[Former Member]

I guess the point I was trying to make was that you should start by just building something that works. You can try to make it 'smart' later. I am not sure why there is an issue with OT1 and OT2 - can't you just lookup the start_date in the tblSchema table to see if it is a holiday/extra day? As far as returning all the values, just store the data in 3 variables and return it as a result set. eg. SELECT overtime1, overtime2, overtime3 from DUMMY

You will probably get more help if you try to get something working and post with specific problems as you go, rather than asking for someone to just write the entire thing for you.

[former_SQLA_member1694869]

This is what I have and I have basically continued with Jason codning. But how do I make this into a workable procedure?

create procedure get_overtime( emp_id, start_date, end_date )
BEGIN

declare overtime1 as int;
declare overtime2 as int;
declare overtime3 as int;
declare dayofweek as smallint;

dayofweek = Datepart( weekday, start_date )

--overtime1

if start_date is an extra day off and emp_id between 100 and 1999 then //I don't understand how to get the date to check the table tblschema
    overtime1 = datediff(minute, end_date, start_date)

else if dayofweek between monday and friday then
    if start_date >= 16:00:00 then
        overtime1 = max(0, datediff(minute, '20:00:00', start_date) ) 
    end if

    if emp_id between 1000 and 1999 then
        if start_date >= 6:00:00 then
            overtime1 += max(0, datediff(minute, min('6:40:00', end_date), start_date) ) 
        endif
    else if emp_id between 100 and 999 or emp_id between 2000 and 2999 then
        if start_date >= 6:00:00 then
            overtime1 += max(0, datediff(minute, min('6:50:00', end_date), start_date) ) 
        endif
    endif
    if dayofweek = friday then
        if start_date > 13:00:00 then
            overtime1 += max(0, datediff(minute, min('16:00:00', end_date), start_date) ) 
        endif
    endif
endif

--overtime2


    if dayofweek between Monday and Thursday then
            if start_date >= 20:00:00 then
                overtime2 = max(0, datediff(minute, '23:59:59', start_date) ) 
            endif
    end if

    if dayofweek between Tuesday and Friday then
            if start_date >= 00:00:00 then
                overtime2 += max(0, datediff(minute, min('06:00:00', end_date), start_date) ) 
            endif
    endif


--overtime3

if dayofweek = Friday then
    if start_date >= 20:00:00 then
        overtime3 = max(0, datediff(minute, '23:59:59', start_date) ) 
    endif
endif

if dayofweek between Saturday and Sunday then
    if start_date between 00:00:00 and 23:59:59 then
        overtime3 +=..?
    endif
endif

if dayofweek = Monday then
    if start_date >= 00:00:00 then
        overtime3 += max(0, datediff(minute, min('06:00:00', end_date), start_date) ) 
    endif
endif


if start_date is an holiday then //I don't understand how to get the date to check the table tblschema
    overtime3 = datediff(minute, end_date, start_date)
endif


// return emp_id, start_date, end_date, overtime1, overtime2, overtime3
END

[former_SQLA_member1694869]

I really need help to turn this into a workable procedure. The logic there but I will not get anywhere.

[Former Member]

Rolle, I think you might benefit from some of the stored procedure tutorials/examples in the documentation. Try a few things out in simple procedures in DBISQL to better understand how to write stored procedures. What you want to do is not terribly difficult, but may take a little time to get exactly right. For example, to get the the tblSchema information, use a select ... into statement. eg. Select <isspecialdaycolumn> INTO <mylocalvar> from tblSchema where tblschema.<datecolumn> = start_date

[former_SQLA_member1694869]

Jason, the procedure itself, I can probably create. It is the logic in that I do not get to so it works in SQL Anyware. Even how to handle tblSchema. I would be extremely grateful if you have the opportunity to show me a working logic for this?

[Former Member]

As I said, going through the tutorials and reading the docs would be useful. Doing lookups in tables from a stored procedure is a common thing and you should be able to find lots of examples of it. However, how is one to get you going. Assuming the following schema for tblSchema:

CREATE TABLE tblschema( emp integer, dtOff date, dayType integer)

The following would be an example of how to get the information on whether or not the day is a holiday/other day in your main procedure:

CREATE PROCEDURE get_overtime( emp_id, start_date, end_date )
BEGIN
declare dayType integer;
--Check the tblSchema table to see if start_date is a vacation day or 
-- extra day off
SELECT IFNull(tblSchema.dayType, 0, tblSchema.dayType)  INTO dayType FROM tblSchema 
  WHERE tblSchema.emp = emp_id and tblSchema.dtOff = start_date
if dayType > 0 and emp_id > 100 and emp_id < 1999 then 
    -- its a holiday/day off
    overtime1 = datediff(minute, end_date, start_date)
endif
...--continue with the rest of your logic.
END

[former_SQLA_member1694869]

Thanks Jason,

I'll try to continue and develop it according to the logic we said. You use Max with a variable, but this is not the sql anyware. What is the equivalent there? Even MIN function.

 max(0, datediff(minute, min('06:00:00', end_date), start_date) )

[Former Member]

greater() and lesser() are the functions you want.

[former_SQLA_member1694869]

Thanks again. I'll try to finish this now. I get a value, however, only overtime2 regardless of day, which feels weird. Nothing you can see immediately what I missed, according to the logic above?

[former_SQLA_member1694869]

I don't understand this. I can't get it work with the logic you help me with

Let say if a employee start to work at 2015-10-08 05:34:00 and end the work at 2015-10-08 20:30:00. (A long day) The employees ID is 1100.

First I check which day it is, and in this case it is Thursday. I also check if it employees specific rule. I check the rules for thursday and it's like this (as I wrote before):

Between 05:34:00 and 06:00:00 on thursday should one rule add to overtime1. 26 min

Between 06:00:00 and 06:40:00 on thursday (if emp_id between 1000 and 1999, in this case) shall another rule add to overtime1. 40 min

Between 20:00:00 and 20:30:00 on thursday should a third rule add to overtime2. 30 min.

The total result for this day should be:

overtime1 = 66 min, overtime2 = 30 min, overime3 = 0 min

I nedd help to fix above logic to my procedure.

[VolkerBarth]

I guess if you want to get more help you will need to create something reproducible including the base tables and some test data.

All we have now are just some snippets of code and some comments was has to be corrected. In other words: We don't have the current code available so how should we be able to tell more?

I would recommend to add that full sample as a separate answer and then apply any fixes directly there instead of a series of comments with code changes...

[former_SQLA_member1694869]

This is what i have...

create table tblSchema (empid int, stype nvarchar(3), sdate datetime);
insert into tblSchema values (1002,'OT1','2015-10-12');
insert into tblSchema values (1002,'OT2','2015-10-13');


create or replace procedure get_overtime( emp_id int, @start_date datetime, @end_date datetime )
begin

declare overtime1 int = 0;
declare overtime2 int = 0;
declare overtime3 int = 0;
declare dayofweek smallint;
declare start_date nvarchar(8);
declare end_date nvarchar(8); 
declare dayType int;

declare local temporary table @daytype (@dtype nvarchar(3)) not transactional;

set start_date = dateformat(@start_date,'hh:mm:ss');
set end_date = dateformat(@end_date,'hh:mm:ss');

set option first_day_of_week = 1; 
set dayofweek = Datepart( weekday, @start_date );

--overtime1

    insert @daytype (@dtype)
    select stype from tblSchema where tblSchema.empID = emp_id and tblSchema.sdate = @start_date and stype IN('OT1','OT2');

    set dayType = (select count(*) from @daytype where @dtype = 'OT2');
    -- it's a day off
    if dayType > 0 and emp_id between 100 and 1999 then

        set overtime1 = overtime1 + datediff(minute, end_date, start_date);
    end if;

 if dayofweek between 1 and 5 then

    if start_date >= '16:00:00' then
        set overtime1 = overtime1 + greater(0, datediff(minute, '20:00:00', start_date) ); 
    end if;

    if emp_id between 1000 and 1999 then
        if start_date >= '6:00:00' then
            set overtime1 = + overtime1 +  greater(0, datediff(minute, lesser('6:40:00', end_date), start_date) ); 
        end if;
            end if;

     if emp_id between 100 and 999 or emp_id between 2000 and 2999 then
        if start_date >= '6:00:00' then
            set overtime1 = overtime1 + greater(0, datediff(minute, lesser('6:50:00', end_date), start_date) ); 
        end if;
    end if;

 end if;

    if dayofweek = 5 then
        if start_date > '13:00:00' then
            set overtime1 = overtime1 + greater(0, datediff(minute, lesser('16:00:00', end_date), start_date) ); 
        end if;
    end if;


--overtime2

    set dayType = (select count(*) from @daytype);

 if dayType = 0 then

    if dayofweek between 1 and 4 then
        if start_date >= '20:00:00' then
            set overtime2 = overtime2 + greater(0, datediff(minute, lesser('23:59:59', end_date), start_date) ); 
        end if;
    end if;

    if dayofweek between 2 and 5 then
        if start_date >= '23:59:59' then
            set overtime2 = overtime2 + greater(0, datediff(minute, lesser('06:00:00', end_date), start_date) ); 
        end if;
    end if;
end if;

--overtime3

   set dayType = (select count(*) from @daytype where @dtype IN('OT1' ) );
   -- it's a holiday
if dayType > 0 and emp_id between 001 and 2999 then

    set overtime3 = overtime3 + datediff(minute, start_date, end_date);
end if;


if dayofweek = 5 then
    if start_date >= '20:00:00' then
        set overtime3 = overtime3 + greater(0, datediff(minute, lesser('23:59:59', end_date), start_date) );
    end if;
end if;


if dayofweek between 6 and 7 then
    if start_date between '00:00:00' and '23:59:59' then
        set overtime3 = overtime3 + greater(0, datediff(minute, lesser('00:00:00', end_date), start_date) );  
    end if;
end if;


if dayofweek = 1 then
    if start_date >= '00:00:00' then
        set overtime3 = overtime3 + greater(0, datediff(minute, lesser('06:00:00', end_date), start_date) ); 
    end if;
end if;


select cast(overtime1 as numeric(16,2)) /60 as overtime1, cast(overtime2 as numeric(16,2)) /60 as overtime2, cast(overtime3 as numeric(16,2)) /60 as overtime3;

end;

Examples of expected values, but that does not work now:

[former_SQLA_member1694869]

select * from get_overtime(1002,'2015-10-08 05:34:00', '2015-10-08 20:30:00'); --Should return-- --overtime1---overtim2---overtime3--- --1,10--------0,50-------0,00-------

select * from get_overtime(1002,'2015-10-12 08:00:00', '2015-10-12 16:30:00');
--Should return--
--overtime1---overtim2---overtime3---
--0,00--------0,00-------8,50-------

select * from get_overtime(1002,'2015-10-13 17:00:00', '2015-10-14 06:30:00');
    --Should return--
    --overtime1---overtim2---overtime3---
    --7,50--------6,00-------0,00-------

[former_SQLA_member1694869]

Someone..?

[chris_keating]

You may want to debug your procedure - Sybase Central has a debugger that can be used.

You logic is flawed. If you look at the statement: select * from get_overtime(1002,'2015-10-08 05:34:00', '2015-10-08 20:30:00');

The '05:34:00' time is always less than the conditionals for start_time that would result in overtime1 being calculated.

[former_SQLA_member1694869]

Thats my problem. I need help with logic so the procedure calculate right...

[former_SQLA_member1694869]

I really need help with the logic that the procedure calculating correctly. I'm going nowhere...

[VolkerBarth]

Have you tried to debug your procedure as Chris has recommended (which is really easy IMHO)?

When trying so, you'll find out that for your sample

select * from get_overtime(1002,'2015-10-08 05:34:00', '2015-10-08 20:30:00')

no OT is calculated as

• your conditions for OT1 checks for "start_date >= '6:00:00'" whereas they should read "start_date < '06:00:00'" (or '06:40:00', I still do not really understand the rules),

• your conditions for OT2 checks for "start_date >= '20:00:00'" whereas it should use the end_date here

• and the datediff() arguments have to be swapped - the starting interval should come before the ending interval if you want to return a positive difference, so instead of "datediff(minute, end_date, start_date)" it should read "datediff(minute, start_date, end_date)" and the like.

If you're not able to notice these flaws by debugging the procedure then I'll guess it will be rather impossible to help further.

[chris_keating]

The expected values are also suspect. You state the expected overtime_1 for '2015-10-08 05:34:00', '2015-10-08 20:30:00' should be 1.1 but that date is not in the sample data so it should be zero. Lets assume a typo and the date is in fact 2015-10-12. The algorithm should result in 50 minutes overtime between 6:00 and 6:50 and 4 hours for the period between 16:00 and 20:00. And I guess that the remaining 30 minutes between 20:00 and 20:30 is simply ignored. That means you have 290 minutes plus the unaccounted 30 minutes. The next example shows no time for overtime_1 for work period 08:00 to 16:30 but that should be 30 minutes based on the algorithm.

The approach that I would take here is to create a function that takes the work hours range (start and end) and the over time range and calculate the overtime. You would simple need to write a procedure that implements the logic to handle the overtime rules and call this function to calculate the overtime for a given period.

Note that the overtime functions needs to address the work hours that 1) do not occur during an overtime range, no overtime calculated 2) either the start time or end time occur during overtime range, set the overtime start as the maximum of work start or over time start and the overtime end as the minimum of work end or over time end. 3) The work period fully overlaps the overtime period.

Here is an example (roughly tested and not optimized):

-- rough attempt at overtime calculator ALTER FUNCTION "DBA"."calc_overtime"( in start_time time, in end_time time, in start_range time, in end_range time ) returns integer begin declare overtime integer = 0; declare calculate_ot bit = 0;

-- only calculate overtime when start and end time overlap an overtime period
if (start_time between start_range and end_range) or (end_time between start_range and end_range) then
   -- start and/or end time occur within the overtime period
    set start_time = greater( start_time, start_range );
    set end_time = lesser( end_time, end_range );
    set calculate_ot = 1;
elseif (start_time <= start_range) and (end_time >= end_range) then
    -- the work period fully overlaps an overtime period
    set start_time = start_range;
    set end_time = end_range;
    set calculate_ot = 1;
end if;

if (calculate_ot = 1) then
    set overtime =  overtime + datediff(minute, start_time, end_time);
end if;

return overtime;

end

[former_SQLA_member1694869]

Thank you for your comprehensive explanation. I will try according to your example.

My example:

select * from get_overtime (1002, '2015-10-08 05:34:00', '2015-10-08 20:30:00')

Should give the following answer in minutes:

--overtime1---overtime2---overtime3
----290----------56 ---------0-----

The date I have written is October 8, 2015. I have for some reason mistyped the results in my example earlier. Above is the right answer to what procedure should return.

[former_SQLA_member1694869]

I've now tested according to your logic and it works really well, Jason. Many thanks! It works fine except when in and out timestamping is on different days. I do not get that right Example:

In = '2015-10-14 20:30:00'
Out = '2015-10-15 02:30:00'

Will provide 6h on overtime2. But now I just get a bunch of negative figures... Do you have a logic to solve this also?

[former_SQLA_member1694869]

I can not solve the logic when in and out time stamping is on different days. Any?

[chris_keating]

You simply need logic that ensures that the start and end datetime are within the fence posts for the overtime period and use datediff.

[former_SQLA_member1694869]

Ok, but I don't know how I do. Can you show an example?

[chris_keating]

An example of fenceposts for this purpose can be found in my calc_overtime function. Here is pseudo code for calculating over_time_2:

// psuedo code Calculate over_time_2
//
//  Monday-Friday 20:00:00 - 06:00:00
//  Assumes that over_time_2 is to 6am Friday morning ONLY
//  after which over_time_3 records starting 8pm Friday
//
// NOTE: calc_overtime is a function previously posted

determine the start date day of week
determine the end date day of week
determine the start time
determine the end time

// over_time_2 is calculated only if the start date 
// is Monday-Thursday. 
  if the start day of week == end day of week // same day
    increment over_time_2 with 
      calc_overtime( start_time, end_time, 
          '20:00:00','23:59:59')   
  else
    if (start day of week plus 1 ) == end day of week   
    // overtime overlaps 2 days
      // add the start day overtime , the final '+ 1' 
      // is for the last minute of day
      increment over_time_2 with 
        calc_overtime(start_time, '23:59:59', 
            '20:00:00', '23:59:59') + 1 

      // add the end day overtime
      increment over_time_2 with 
        calc_overtime( '00:00:00', end_time, 
            '00:00:00', '06:00:00')
     else
       // assume that shift cannot be greater than 24 hours
       // otherwise, add 24 hours per day between 
       // start and end date
     end if
  end if
end if

[former_SQLA_member1694869]

Thanks again for your help! The logic is what I need, but I want this in my main procedure and not create another procedure for this. I have try to embed this logic for all three overtime intervals in the main procedure, but I do not get the right data. Have any tips on how I should do it the best way?

[chris_keating]

Please post what you have and what the nature of the"do not get the right data" problem.

[VolkerBarth]

The logic is what I need, but I want this in my main procedure and not create another procedure for this.

Is there a particular reason you want to "have it all" in one procedure? - I'm asking because a procedure can easily call a second one (and the caller of the first procedure would not know that a second "inner" procedure is called), and because your obvious struggle with the calculation's logic might suggest that it would be helpful to split up the logic in smaller parts ("divide and conquer").

That being said, I surely second Chris's latest comment. Feel free to EDIT your answer by applying the current procedure's code. The forum is based on a Wiki system, and with the help of the "edited..." link below the answer, it's quite easy to note the differences to the previous version - you can use it quite like a version control system...

[former_SQLA_member1694869]

The reason I want one procedure and not a number of procedures that call each other, I thought it would be easier to manage that way. But perhaps it's easier to divide this into a main procedure and a procedure for the "day switch" for each of the overtime intervals. Anyway, it's like I wrote earlier, I will not get anywhere in this and have nothing more to show than the one I posted earlier here, and the help I got from Chris. So I am still in need of help.

[chris_keating]

I am not sure then how we can help you further. I have provided code samples that show working samples. I am not able to write this code in complete on your behalf as that would take time from my own work responsibilities. I are willing to review and comment on what you have and what you describe as "do not get the right data".

[former_SQLA_member1694869]

I have developed this for a while now and I think I soon will have a solution. However, I am not clear about what you mean by:

 increment over_time_2 with

What is the equivalent in Sybase?

[chris_keating]

Add calc_overtime to over_time_2

[former_SQLA_member1694869]

Perfect. Now works overtime_2 for me, on stamping area are on different days. I have tried to use the same code for overtime3, it's the same time I thought. But I get negative figures. Possibly because the condition extends over several days. Do you have a smart solution for overtime3 also in cases stamping area extends over several days?

        over_time_3:                        
        Friday evening – Monday morning         20:00:00 - 06.00:00

[chris_keating]

show your code and we will review. As noted, I have my own responsibilities and do not have the cycles to spend any additional time preparing samples for this topic.

[former_SQLA_member1694869]

Ok, this is what I have. What have I missed?

if dayofweek = 5 then //Friday
    if start_date = end_date then
        set overtime3 = overtime3 + calc_overtime(start_time, end_time, '20:00:00', '23:59:59');
    else
        if (start_date + 1 ) = end_date then  
        set overtime3 = overtime3 + calc_overtime(start_time, '23:59:59', '20:00:00', '23:59:59') + 1 ; 
end if;  
    end if;
        end if;

if dayofweek between 6 and 7 then //Saturday and Sunday
    if start_date = end_date then
        set overtime3 = overtime3 + calc_overtime(start_time, end_time, '00:00:00', '23:59:59');
    else
        if (start_date + 1 ) = end_date then  
        set overtime3 = overtime3 + calc_overtime('00:00:00', end_time, '00:00:00', '23:59:59') + 1;
        set overtime3 = overtime3 + calc_overtime(start_time, '23:59:59', '00:00:00', '23:59:59') + 1;

end if;  
    end if;
        end if;

if dayofweek = 1 then // Monday
    if start_date = end_date then
        set overtime3 = overtime3 + calc_overtime(start_time, end_time, '00:00:00', '06:00:00');
    else
        if (start_date + 1 ) = end_date then    
        set overtime3 = overtime3 + calc_overtime('00:00:00', end_time, '00:00:00', '06:00:00');    
end if;  
    end if;
        end if;

[chris_keating]

1) If you have a shift that goes from Friday to Saturday, you currently only calculate the Friday part of the shift.

2) If you have a shift that that runs from Sunday to Monday, you have to adjust the end_time to match the end of that shift period for Monday.

3) Your code for Monday-only is not correct. If the shift goes into Tuesday, you cannot use end_time - it must be the end of the shift period for Monday.

4) There is an off-by-1 issue in the calculation of overtime if the shift period extends over two days. My +1 was to address the last minute of the day for a shift on first day. That same +1 is not needed on the second day unless the work period ends after 23:59:00. I assume you would add 1 at that point but there is no rule defined that addresses partial minutes.

There may be other logic errors but I have not exhaustively debugged/tested your code. I leave that you to do - had the code been debugged, the items identified above should have stood out.