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.

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

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.

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

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

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

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

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?

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

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

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.

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?

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

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.

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;

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.