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.
|
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:
... 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?
(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 <isspecialdaycolumn> INTO <mylocalvar> from tblSchema where tblschema.<datecolumn> = 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:
(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.
(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
|
This is what i have...
Examples of expected values, but that does not work now: 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-------
(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
no OT is calculated as
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;
end
(30 Oct '15, 11:09)
Chris Keating
Thank you for your comprehensive explanation. I will try according to your example. My example:
Should give the following answer in minutes:
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:
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
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:
What is the equivalent in Sybase?
(29 Nov '15, 14:48)
Rolle
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?
(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?
(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
|
Do you have any specific question related to this task, otherwise it seems to me that we schould do your homework...
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.
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 :)
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.
Have I explained clearly enough or it is still unclear what I mean?