CODEWITHSUNDEEP
phpmysqlcount
jonathan young
jonathan young

Reputation: 237

Count holiday days from start date for a year then every year

HI how would i go about counting days holiday taken in one year from the employee start date then every consecutive after that. 

Table 1 

id  name    lastname     startdate      holidays

1    jon     homes        2014-10-10      25

Holiday table will have all holidays taken

id   empid   date

1       1    2014-12-20
2       1    2014-12-21
3       1    2014-12-22
4       1    2015-10-01
5       1    2015-10-02
6       1    2015-10-11 (this would be a new year)

How can I query mysql to count holidays for 12 months form the employees start date then do this every year in the future without hard coding the dates.

I can understand how to do this in the current year

SELECT COUNT(*)
 FROM Holiday
 WHERE YEAR(date) = YEAR(CURDATE()) AND empid = 1;

So how do i run the query from the startdate 2014-10-10 for 12 months then for every year (the next time the start date would be 2015-10-10 then 2016-10-10 etc.)

Thanks

Upvotes: 2

Views: 399

Answers (2)

CodeGodie
CodeGodie

Reputation: 12132

You need to consider some math for this query. For this to work, I will take the difference in your dates holiday_date - emp_startdate and divide that by the days in a year result/365, finally use CEIL to round up.

I would also use JOIN and GROUP BYin this manner:

SELECT 
    e.name AS Employee,
    CEIL(DATEDIFF(h.date, e.startdate)/365) as Year,
    count(h.date) as Holidays_Taken
FROM employees AS e
LEFT JOIN holidays_taken AS h ON e.id = h.empid
WHERE e.id = 1
GROUP BY Year

Result:

+----------+------+---------------+
| Employee | Year | Holidays_Taken|
+----------+------+---------------+
| Jon      | 1    | 5             |
+----------+------+---------------+
| Jon      | 2    | 1             |
+----------+------+---------------+

DEMO: SQL FIDDLE

Upvotes: 0

ThinkTank
ThinkTank

Reputation: 1191

Just use a BETWEEN clause :

SELECT COUNT(*)
 FROM Holiday
 WHERE date BETWEEN '2015-10-10' AND '2016-10-10' AND empid = 1;

Dynamic way : 

SELECT * 
FROM Holidays 
WHERE date BETWEEN DATE_SUB(CONCAT(YEAR(CURDATE()), '-10-10'), INTERVAL 1 YEAR) AND CONCAT(YEAR(CURDATE()), '-10-10') 
AND empid = 1;

Edit : its the contrary, you must start from year -1.

Upvotes: 0

Related Questions