Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 06-11-2009
Member
 
Join Date: Nov 2009
Posts: 620
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.
Reply With Quote
  #2  
Old 06-11-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
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.
Reply With Quote
  #3  
Old 06-11-2009
Member
 
Join Date: May 2008
Posts: 2,008
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.
Reply With Quote
  #4  
Old 06-11-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
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 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
Reply With Quote
  #5  
Old 06-11-2009
Member
 
Join Date: May 2008
Posts: 2,293
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 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.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


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 16-02-2012 08:05 PM
How to calculate pay period between interval of 15 days Bala!aditya MS Office Support 2 28-01-2012 03:43 PM
Program to calculate days difference between two dates in java. KADRI Software Development 5 16-03-2011 06:22 PM
How to create 7 days query in MySql Patrickboy Software Development 2 18-05-2009 02:02 PM
How to calculate duration in days aileen Microsoft Project 3 26-09-2008 08:28 PM


All times are GMT +5.5. The time now is 08:36 PM.