

 Thread Tools  Search this Thread 
#1
 
 
Calculate Working days in MySql
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. 
#2
 
 
Re: Calculate Working days in MySql
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. 
#3
 
 
Re: Calculate Working days in MySql
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. 
#4
 
 
Re: Calculate Working days in MySql
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 nonSaturday (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 
#5
 
 
Re: Calculate Working days in MySql
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 variablelength 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. 

Tags: databse, micorsoft access, my sql, orcale, syntax 
Thread Tools  Search this Thread 

Similar Threads for: "Calculate Working days in MySql"  
Thread  Thread Starter  Forum  Replies  Last Post 
Need a formula to calculate years/days/months among several date in Excel  Kumar esan  MS Office Support  2  16022012 08:05 PM 
How to calculate pay period between interval of 15 days  Bala!aditya  MS Office Support  2  28012012 03:43 PM 
Program to calculate days difference between two dates in java.  KADRI  Software Development  5  16032011 06:22 PM 
How to create 7 days query in MySql  Patrickboy  Software Development  2  18052009 02:02 PM 
How to calculate duration in days  aileen  Microsoft Project  3  26092008 08:28 PM 