Show average hours in specific periods using SQL

Question

I am trying to display average vacation days and sick days for 3 periods.

So far I have this SQL query:

SELECT AVG(VACATIONHOURS) AS 'Vacation hours', AVG(SICKLEAVEHOURS) AS 'SICK DAYS'
FROM HUMANRESOURCES.EMPLOYEE E
WHERE E.BIRTHDATE BETWEEN '01-01-1960' AND '12-31-1969'
UNION ALL
SELECT AVG(VACATIONHOURS) AS 'VACATION', AVG(SICKLEAVEHOURS) AS 'SICK DAYS'
FROM HUMANRESOURCES.EMPLOYEE E
WHERE E.BIRTHDATE BETWEEN '01-01-1970' AND '12-31-1979'
UNION ALL
SELECT AVG(VACATIONHOURS) AS 'VACATION', AVG(SICKLEAVEHOURS) AS 'SICK DAYS'
FROM HUMANRESOURCES.EMPLOYEE E
WHERE E.BIRTHDATE BETWEEN '01-01-1980' AND '12-31-1989'
;

My output only has 2 columns sick and vacation day and 3 rows under with numbers.

My question is how to add a 3rd column that will show years?

I need that for report builder, so I can represent that information correctly

Answer 1

I hope you are looking to add the year in the where clause then just add max(years)

SELECT AVG(VACATIONHOURS) AS 'Vacation hours', 
       AVG(SICKLEAVEHOURS) AS 'SICK DAYS', 
       max('1960 to 1969')
 FROM HUMANRESOURCES.EMPLOYEE E
WHERE E.BIRTHDATE BETWEEN '01-01-1960' AND '12-31-1969'

Answer 2

I would suggest that you put these into groups. I would just do:

SELECT CONCAT(YEAR(BIRTHDATE), 's') as decade,
       AVG(VACATIONHOURS) AS `Vacation hours`,
       AVG(SICKLEAVEHOURS) AS `SICK DAYS`
FROM HUMANRESOURCES.EMPLOYEE E
GROUP BY decade
ORDER BY decade;