Как посчитать количество присутствующих сотрудников за каждый месяц?
Я ищу SQL для подсчета количества сотрудников, которые работали каждый месяц в этом году. Значит, результат должен быть для отдела контроля качества в 2013 году, например:
dept_name Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec
QA ( **count of working employees in each month** )
Стол сотрудника:
id dept_id emp_name date_started date_terminated
1 3 Amstrong 01-Apr-13 null
2 4 Blowing 03-Jun-13 null
3 3 Jerry 09-Sept-13 01-May-14
4 3 Smack 04-Jun-12 null
5 4 Zuckus 13-Aug-12 13-Dec-13
6 4 Kolman 19-Nov-12 null
7 3 Qual 15-Oct-12 02-Nov-13
8 4 John 11-Jul-11 06-Apr-13
9 4 Delta 28-Jan-14 null
10 3 Panther 17-Jul-14 null
11 3 Amay 01-Apr-13 null
12 4 Blue 03-Jun-13 null
13 3 Jack 09-Sept-13 01-May-14
14 3 Stack 04-Jun-12 null
15 4 Jasus 13-Aug-12 13-Dec-13
Столовый отдел:
id dept_name
1 IT
2 Support
3 QA
4 Development
5 Admin
Запрос, который я пытался получить результат:
SELECT
d.dept_name,
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 1 THEN e.id END) as "Jan",
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 2 THEN e.id END) as "Feb",
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 3 THEN e.id END) as "Mar",
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 4 THEN e.id END) as "Apr",
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 5 THEN e.id END) as "May",
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 6 THEN e.id END) as "Jun",
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 7 THEN e.id END) as "Jul",
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 8 THEN e.id END) as "Aug",
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 8 THEN e.id END) as "Sep",
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 10 THEN e.id END) as "Oct",
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 11 THEN e.id END) as "Nov",
COUNT (CASE WHEN EXTRACT(MONTH FROM e.date_started) = 12 THEN e.id END) as "Dec"FROM department d
JOIN employee e ON (d.id = e.dept_id)
WHERE EXTRACT(month FROM e.date_started) BETWEEN 01 AND 12
AND EXTRACT(year FROM e.date_started) < 2014
AND (e.date_terminated IS NULL OR EXTRACT(month FROM e.date_terminated) > EXTRACT(month FROM NOW()))
AND (e.date_terminated IS NULL OR EXTRACT(year FROM e.date_terminated) >= EXTRACT(year FROM NOW()))
GROUP BY d.name
ORDER BY d.name ASC
Но это касается только работника, который начал работать в этом конкретном месяце
Решение
Вот еще один способ сделать это: этот код предоставит количество сотрудников за январь и февраль для каждого департамента за 2013 год. Здесь он представлен в скрипте sql http://sqlfiddle.com/#!2/4a861f/14
SELECT a.dept_name, coalesce(Jan.total,0) as Jan, coalesce(Feb.total,0) as Feb
FROM department as a
LEFT JOIN
(SELECT dept_id, COUNT(emp_name) as total from employee
where date_started <= '2013-01-31 00:00:00'
and (date_terminated >= '2013-01-01 00:00:00'
or date_terminated is null) group by dept_id) as Jan
ON Jan.dept_id = a.id
LEFT JOIN
(SELECT dept_id, COUNT(emp_name) as total from employee
where date_started <= '2013-02-31 00:00:00'
and (date_terminated >= '2013-02-01 00:00:00'
or date_terminated is null) group by dept_id) as Feb
ON Feb.dept_id = a.id
Другие решения
У вас должен быть столбец в вашей таблице, например, ЖУРНАЛ, чтобы вы могли по-настоящему проверить, кто работал в данном месяце, в настоящий момент невозможно проверить, кто работал в каком месяце, вы можете проверить только, сколько сотрудников было создано в данном месяце. или сколько сотрудников прекратили работать в данном месяце.
Но так должен выглядеть запрос только в том случае, ГДЕ вы должны добавить новый столбец сотрудников, в котором хранится журнал присутствующих сотрудников на работе …..
SELECT
COUNT(employee.id) as 'Number of EMployees',
department.dept_name as 'Department name'
FROM employee
LEFT JOIN department on employee.dept_id=department.id
WHERE
employee.dateLog > 2013-01-01
employee.dateLog < 2013-02-01
Вы можете сгенерировать дату в каждом месяце и найти количество сотрудников, которые работают в эту дату. Формат будет иметь одну строку на отдел в месяц. Поворот результатов в отдельной операции, возможно, лучше сделать на прикладном уровне:
with months as (
select generate_series('2013-01-01'::timestamp, '2013-12-01'::timestamp, '1 month') as m
),
select d.dept_name, m.m, count(e.dept_id) as numemployees
from department d cross join
months m left join
employee e
on e.dept_id = d.id and e.date_started <= m.m and
(e.date_terminated is null or e.date_terminated >= m.m)
group by d.dept_name, m.m
order by d.dept_name, m.m;