Format MySQL using CONCAT

Question

Thanks to some help I have a MySQL query that starts on 2014-10-10 and fines the amount of holidays in 12 months period eg 2014-10-10 to 2015-10-09 then 2015-10-10 to 2016-10-09

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

With a result

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

Is it possible to have the year show 2014-10-10 to 2015-10-09 instead of year 1 then 2015-10-10 to 2016-10-09 for year 2

Here's my SQL FIDDLE

Thanks

Have you tried adding the dates to the return field list? Like: `SELECT h.date, e.startdate, e.name [...]`? — FirstOne, Oct 13 '15 at 17:33
@CodeGodie I have a fiddle [demo](http://sqlfiddle.com/#!9/371a7/1) — jonathan young, Oct 13 '15 at 17:37
Just an observation, but it looks like you've given yourself a leap-year bug. Every fourth year has 366 days. When possible, always try to use built-in functions for date & time manipulations. — AWinkle, Oct 13 '15 at 17:58

score 0 · Answer 1 · answered Oct 13 '15 at 17:56

0

Not sure I've got your goal, but here is my approach:

http://sqlfiddle.com/#!9/371a7/14

SELECT 
    e.name AS Employee,
    @year := CEIL(DATEDIFF(h.date, e.startdate)/365) AS Year,
    CONCAT(DATE_ADD(e.startdate, INTERVAL @year-1 YEAR),' - ',DATE_ADD(e.startdate, INTERVAL @year YEAR)),
    COUNT(h.date) AS Holidays_Taken,
    SUM(h.hours) AS Hours
FROM employees AS e
LEFT JOIN holidays_taken AS h ON e.id = h.empid
WHERE e.id = 1 
GROUP BY Year

answered Oct 13 '15 at 17:56

Alex

16,739
1
28
51

Great answer but what about leap years the next one is 2016 with 366 days in it? – jonathan young Oct 13 '15 at 20:17
Thanks will take a look and try to implement it into your query – jonathan young Oct 13 '15 at 20:59

score 0 · Accepted Answer · answered Oct 13 '15 at 17:57

0

I would create the CONCAT in this manner:

SELECT 
    e.name AS Employee,
    CONCAT(
        CEIL(DATEDIFF(h.date, e.startdate)/365), 
        ' (',
        DATE_ADD(e.startdate, INTERVAL FLOOR(DATEDIFF(h.date, e.startdate)/365) YEAR), ' to ',        
        DATE_ADD(e.startdate, INTERVAL CEIL(DATEDIFF(h.date, e.startdate)/365) YEAR),
        ')'
        ) as Year,
    COUNT(h.date) AS Holidays_Taken,
    SUM(h.hours) AS Hours
FROM employees AS e
LEFT JOIN holidays_taken AS h ON e.id = h.empid
WHERE e.id = 1
GROUP BY Year

DEMO: SQL FIDDLE

answered Oct 13 '15 at 17:57

CodeGodie

12,116
6
37
66

[SQL FIDDLE](http://sqlfiddle.com/#!9/c9f2a/4) In the employee table holidays(hours) = 250 how do a sum holidays - hours I have tried on the fiddle demo thanks for your help – jonathan young Oct 13 '15 at 18:41
What about leap years 2016 the next one with 366 days in it? – jonathan young Oct 13 '15 at 20:16

Format MySQL using CONCAT

2 Answers2

Linked