|
|
![]() |
| 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 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 |
#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 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. |
![]() |
|
Tags: databse, micorsoft access, my sql, orcale, syntax |
Thread Tools | Search this Thread |
|
![]() | ||||
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 |