2

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

jonathan young
  • 237
  • 2
  • 11

2 Answers2

0

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.

ThinkTank
  • 1,187
  • 9
  • 15
  • It depends, do you use PHP ? Is is supposed to be a stored procedure ? Do you prefere to use `DATE_ADD(date, INTERVAL 1 YEAR) ` – ThinkTank Oct 13 '15 at 14:37
  • Edited : Its not year + 1 but year -1. You cant get year +1 because it doesnt exist yet ^^ – ThinkTank Oct 13 '15 at 14:52
  • not sure what you mean: not year + 1 but year -1 I would like to do the query the dynamic way? INTERVAL - 1 YEAR – jonathan young Oct 13 '15 at 15:40
  • Thanks for you response just ran your dynamic query I have setup a demo [DEMO](http://www.sqlfiddle.com/#!9/2b418/4) as you can see it pulls the date 2014-10-24 – jonathan young Oct 13 '15 at 16:14
0

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

CodeGodie
  • 12,116
  • 6
  • 37
  • 66
  • OK I am having problems getting over the fact that if you run the query on on 2015-12-30 the on 2016-1-1 the current year changes and the between date 2015-10-10 to 2016-10-10 is over 2015 and 2016 I have setup a demo on [sqlfiddle](http://www.sqlfiddle.com/#!9/2b418/5) I will take alook at your solution thank you – jonathan young Oct 13 '15 at 16:25
  • I see. Have a look at my fiddle first. And tell me what you think – CodeGodie Oct 13 '15 at 16:28
  • Cheers for the fiddle demo as you can see in the fiddle demo on the comment i made my table as the amount of hours taken on that day how do i count them instead of the days? [DEMO](http://www.sqlfiddle.com/#!9/2b418/5) Thanks – jonathan young Oct 13 '15 at 16:33
  • What? im confused now. You didnt mention anything about `hours` in your original question. How would the hours differ from the startdate? Please provide detailed description and if too different from the initial question, then open up another question so we can follow up. – CodeGodie Oct 13 '15 at 16:35
  • sorry about the hours but I was concentrating on the year and date Your demo does the hours great stuff. One other thing in the year row instead of year 1 year to how do i format it so it says 2014-10-10 to 2015-10-10 (year 1 ) 2015-10-10 to 2016-10-10 (year 2) is this possible in the query? Thanks again!! – jonathan young Oct 13 '15 at 16:54
  • Glad I could help. Im sure it can be done, prob with CONCAT. I suggest opening up a new question for this one. Then link me to it so that I can continue helping. – CodeGodie Oct 13 '15 at 17:03
  • How do a link you in new question [Link](http://stackoverflow.com/questions/33108765/format-mysql-using-concat) the question thanks – jonathan young Oct 13 '15 at 17:20
  • What about leap years 2016 the next one with 366 days in it? – jonathan young Oct 13 '15 at 20:18
  • How do i get al users holidays group by years is this possible [FIDDLE](http://sqlfiddle.com/#!9/cc6ab8/1) the demo as 2 users 1 and 18 (i have 18 users all together ) thanks – jonathan young Oct 14 '15 at 15:59