Employee Year Calculation 1

[jrahma]

Hi,

I have employees table with date_of_join field

and I have employee_leaves table with the following fields:

employee_id
leave_from
leave_to
total_days

the employee joined on 15 Feb 2011

I want to have a query showing the cound of leaves for every employee years based on his date_of_join

for example, if the employee joined on 15 Feb 2011 then the result will be like this:

Feb 2011 to feb 2012 ---- totals days: 21
Feb 2012 to feb 2013 ---- totals days: 26
Feb 2013 to feb 2014 ---- totals days: 8

where Feb to feb is the employee year so it's from 15 Feb to 14 Feb every year

can anyone help please?



Jassim Rahma

 

[ChrisHirst]

showing the cound of leaves

Means what exactly??

Feb 2011 to feb 2012 ---- totals days: 21
Feb 2012 to feb 2013 ---- totals days: 26
Feb 2013 to feb 2014 ---- totals days: 8

What is the correlation/rationale between years

because;
Year 1 == 21 days
then +5 for;
year 2 == 26 days
the -18 for;
year 3 == 8 days

Has no logical sequence to it to derive a query formula from.


Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[jrahma]

there is no relation. The numbers are just how many day offs the employee takes in each year so it's just an example and you can replace it with any number. For example, I took 10 days in 2012, 30 days in 2013 and 8 days in 2014


Jassim Rahma

 

[vacunita]

This is not something likely to be accomplished in a single query.

If I understand correctly, you would need to calculate the amount of years he's been in the company, and then construct specific queries for each time period on the [employee_leaves] table. So it would require a loop to generate the yearly time periods for the base query of:

SELECT SUM(total_days) where employee_id=xxx and leave_from > date_of_join and leave_to < (date_of_join + one year).

then

SELECT SUM(total_days) where employee_id=xxx and leave_from > (date_of_join + 1 year) and leave_to < (date_of_join + 2 years).

etc...

Basically you really need to do this from the client side code, so you can get the data you want from the leaves table while manipulating the dates you want to get.

It may be possible to construct a Stored procedure to do this, but a single Query, is not very likely.







----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[jrahma]

from client side? you mean my .NET application?

How about stored procedure? do you an example on how to start with this query / loop?





Jassim Rahma

 

[vacunita]

from client side? you mean my .NET application?

Whatever you are using to connect to the DB is the client. If that is your NET application then yes. Doing it from there makes the most sense.

Basically what you want to do, is dynamically craft the queries from within .NET.

1. Query to get the employee's join_date.
2. Then based on that calculate how many years he's been with the company,
3. Craft the additional queries based on that number to get the date ranges.

From a stored procedure stand point it would be the same, only using MYSQL's scripting language which may not be as powerful as.NET.

http://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[jpadie]

not to put the cat amongst the pigeons, but at first glance cannot this be done (for an employee) by grouping by a separate date table?

in pseudo sql

select d.datefield , // could add some logic here to make this a nice label
       e.employee_id,
       e.date_joined
       sum(h.total_days)
from   datetable d
join   leave_table h
on     cast(h.leave_from as date) = d.datefield
join   employee_table e
on     e.employee_id = h.employee_id
group by floor(unix_timestamp(d.datefield)/31536000)
having e.employee_id = ???
and d.datefield >= e.date_joined

it's a bit clunky as it does not take into account leap years.
also it does not take into account edge cases where the start date/end date crosses a year; you could probably do this by embedding the sum() within a case statement that compares against the value of a date_add(d.datefield).

However, as often the case the schema makes life hard. Change the schema to something like this and life becomes much easier

create table employee_leave(
 leaveDate date,
 employeeID int(10), 
 leaveTypeID int(2), 
 fh decimal(1,1)
);
create unique index employee_leave_index on employee_leave (
 leaveDate, 
 employeeID
);
create table leaveType(
  leaveTypeID int(2) primary key auto_increment,
  leaveType varchar(50)
);

[/code]

where the date is the leave date (one entry per leave day taken), leavetype would be a lookup to a table of types of leave like holiday, parental, compassionate, unauthorised etc, and fh is full/half days to cater for small absences. With a table like that the analysis becomes significantly easier.

 

[jpadie]

or perhaps even easier and without a date table

SELECT   e.employeeID, 
         d.datefield & " to " & DATE_ADD(d.datefield, INTERVAL 1 YEAR) as "Period"
         SUM(CASE
          WHEN CAST(h.holidayStart AS DATE) >= e.datefield AND CAST(h.holidayEnd AS DATE) <= DATE_ADD(d.datefield, INTERVAL 1 YEAR)
          THEN TO_DAYS(CAST(h.holidayEnd AS DATE)) - TO_DAYS(CAST(h.holidayStart AS DATE)
          WHEN CAST(h.holidayStart as DATE) < d.datefield AND CAST(h.holidayEnd AS DATE) <= DATE_ADD(d.datefield, INTERVAL 1 YEAR)
          THEN TO_DAYS(CAST(h.holidayEnd AS DATE)) - TO_DAYS(d.datefield)
          WHEN CAST(h.holidayStart as DATE) < d.datefield AND CAST(h.holidayEnd AS DATE) > DATE_ADD(d.datefield, INTERVAL 1 YEAR)
          THEN TO_DAYS(DATE_ADD(d.datefield, INTERVAL 1 YEAR)) - TO_DAYS(d.datefield)
          ELSE TO_DAYS(CAST(h.holidayEnd AS DATE)) - TO_DAYS(CAST(h.holidayStart AS DATE)
        END CASE) AS "Leave Taken in Period"
FROM     employeeTable e
JOIN     holidayTable h
ON       e.employeeID = h.employeeID 
         AND 
         CAST(h.holidayStart AS DATE) BETWEEN e.joinDate AND DATE_ADD(e.joinDate, INTERVAL 1 YEAR)
JOIN     datetable d
ON       d.datefield >= CAST(h.holidayStart AS DATE) OR d.datefield <= CAST(h.holidayEnd AS DATE)
GROUP BY e.employeeID,
         CASE
            WHEN MONTH(d.datetime) >= 3
            THEN FLOOR(TO_DAYS(d.datetime)/DAYOFYEAR(YEAR(d.datetime) + 1 & "-12-31") )
            ELSE FLOOR(TO_DAYS(d.datetime/DAYOFYEAR(YEAR(d.datetime) & "-12-31"))
         END CASE
ORDER BY e.joinDate ASC

again, not tested this but if you want to supply a schema and some data in sql insert format i am happy to have a play this evening. I'm not certain that case statements can be used in the group by context; if not then it might be workable with an IF construct.