Calculate Working days in MySql

[DeMario]

Hi,
Does any one know how to calculate working days from a MySQL database. We here have a list of long employees with there attendance sheet. The sheet gives out value if 1 for present and 0 of those who had not worked on the day. What syntax here I can use to calculate the working days at a single time of all the employees. Is there a way also to compare two dates of a particular table. Please post suggestions with example on this. I also want to know about Oracle in MySql.

[Praetor]

First you will have to declare that you have create a function, in this case it is called Diff Days and receives two parameters, the starting date of the period and the final. CREATE FUNCTION DiffDays (@ StartDate DATETIME, @ EndDate DATETIME). Then RETURNS integer AS Begin, with this variable we calculate how many days 'normal' is in the range of dates. INT DECLARE @ DaysBetween, with this variable we accumulate the total days. INT DECLARE @ businessdays, this variable serves as a counter to know when we get to the last day of the range. DECLARE @ Cnt INT, this variable is used for comparing to see if this day is Saturday or Sunday calculation.

[Katty]

Here are some variables you can use to work with dates. If you familiar with MySQL then this are easy to apply. The only thing is important is the variable implementation. First this pair of variables used to compare the two dates, if they match, the function returns us a 0 *, DATETIME DECLARE @ EvalDate. Then Initialize some variables by SELECT @ DaysBetween = 0, SELECT @ businessdays = 0 & SELECT @ Cnt = 0. To calculate how many days are in the normal range of dates SELECT @ DaysBetween = DATEDIFF (DAY, @ StartDate, @ EndDate) + 1.

[kelfro]

In order to compare two dates and get result out of it you will need to apply this syntax. For Comparing two dates you will need to apply IF @ ini <> @ end BEGIN. If the timing difference is equal to two, is because only one day has passed, so it only validates that will not make days more. The syntax will look some thing like this,
IF @ DaysBetween = 2
BEGIN
SELECT @ businessdays = 1
END
ELSE
BEGIN
WHILE @ Cnt <@ DaysBetween
BEGIN
Use the DatePart function with the parameter estimates dw that day of the week contains a certain date, certain non-Saturday (7) or Sunday (1). If not Saturday or Sunday, then one to the total amount of days you want displayed then,
SELECT @ businessdays = @ businessdays + 1
END
After that add a day over the counter -
SELECT @ Cnt = @ Cnt + 1
END
END
END
ELSE
BEGIN

[Zecho]

Some features here you can utilize to work with Oracle and MySql. It is necessary to understand the type of query used under this condition. SQL enables communication with the management system database. So here for Allows querying of data the data types used are, CHAR - They have a fixed length. 1 to 255 stores. If you enter a string of less than the defined length were filled with white to the right until it is completed. If you enter a string longer than the set we will fail. VARCHAR - Stores variable-length strings. Maximum length is 2000 characters. If you enter a shorter chain than this set, is stored with that length and not filled with white or any other character right to the length defined.If you enter a string longer than that fixed, we will fail. NUMBER - Both are stored as decimal integers. Number (precision, scale). LONG - Do not store large numbers, but strings of up to 2 GB. DATE - Store date. It is stored as follows: Century / Year / Month / Day / Hour / Minutes / Seconds.