# Calculate dates

 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`````` asked 10 Oct '15, 14:18 Rolle 439●31●34●46 accept rate: 0% 2 Do you have any specific question related to this task, otherwise it seems to me that we schould do your homework... (12 Oct '15, 07:15) Martin 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. (12 Oct '15, 15:13) Rolle Replies hidden 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 :) (12 Oct '15, 16:10) Vlad 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----`````` (12 Oct '15, 16:46) Rolle Have I explained clearly enough or it is still unclear what I mean? (13 Oct '15, 11:07) Rolle

 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`````` ... answered 14 Oct '15, 13:46 Jason Hinspe... 2.7k●6●34●47 accept rate: 35% 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. (14 Oct '15, 16:24) Rolle Replies hidden @Rolle: Please help others by stating whether Jason has understood your logic correctly or not... (15 Oct '15, 05:53) Volker Barth Jason has understood it correct. I just wish to complete it for the other intervals... (15 Oct '15, 09:43) Rolle 3 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. (15 Oct '15, 11:39) Jason Hinspe... 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`````` (16 Oct '15, 15:58) Rolle Replies hidden I really need help to turn this into a workable procedure. The logic there but I will not get anywhere. (20 Oct '15, 13:47) Rolle 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 INTO from tblSchema where tblschema. = start_date (20 Oct '15, 13:50) Jason Hinspe... 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? (20 Oct '15, 14:55) Rolle Replies hidden 1 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 `````` (21 Oct '15, 09:50) Jason Hinspe... 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) )`` (21 Oct '15, 15:03) Rolle greater() and lesser() are the functions you want. (21 Oct '15, 16:28) Jason Hinspe... 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? (22 Oct '15, 14:57) Rolle 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. (23 Oct '15, 10:30) Rolle 1 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... (23 Oct '15, 11:06) Volker Barth More comments hidden showing 5 of 14 show all flat view
 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: answered 23 Oct '15, 16:59 Rolle 439●31●34●46 accept rate: 0% Volker Barth 31.6k●321●465●678 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-------`````` (23 Oct '15, 17:00) Rolle Someone..? (28 Oct '15, 06:09) Rolle Replies hidden 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. (28 Oct '15, 10:20) Chris Keating Thats my problem. I need help with logic so the procedure calculate right... (28 Oct '15, 15:34) Rolle I really need help with the logic that the procedure calculating correctly. I'm going nowhere... (30 Oct '15, 04:45) Rolle 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. (30 Oct '15, 08:20) Volker Barth 1 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 (30 Oct '15, 11:09) Chris Keating 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. (30 Oct '15, 16:36) Rolle 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? (31 Oct '15, 16:55) Rolle I can not solve the logic when in and out time stamping is on different days. Any? (03 Nov '15, 16:56) Rolle You simply need logic that ensures that the start and end datetime are within the fence posts for the overtime period and use datediff. (04 Nov '15, 08:48) Chris Keating Ok, but I don't know how I do. Can you show an example? (04 Nov '15, 08:55) Rolle 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``` (04 Nov '15, 10:19) Chris Keating 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? (07 Nov '15, 06:42) Rolle Please post what you have and what the nature of the"do not get the right data" problem. (09 Nov '15, 09:06) Chris Keating 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... (09 Nov '15, 10:11) Volker Barth 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. (12 Nov '15, 16:52) Rolle 2 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". (13 Nov '15, 10:00) Chris Keating 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? (29 Nov '15, 14:48) Rolle 1 Add calc_overtime to over_time_2 (30 Nov '15, 08:44) Chris Keating 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`````` (07 Dec '15, 16:50) Rolle 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. (10 Dec '15, 08:26) Chris Keating 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;`````` (12 Dec '15, 15:21) Rolle 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. (14 Dec '15, 08:47) Chris Keating More comments hidden showing 5 of 24 show all flat view
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×107

question asked: 10 Oct '15, 14:18

question was seen: 1,122 times

last updated: 14 Dec '15, 09:39