Блог

У меня есть 2 таблицы employees и Medical leave, которые связаны идентификатором Employee, и в основном таблица Medical leave будет содержать несколько данных об одном сотруднике, который берет несколько отпусков, я хочу отфильтровать данные по месяцу медицинского отпуска и включить сотрудников, отпуск по болезни которых не приходится на отфильтрованный месяц, в качестве нулевого значения.

        EMPLOYEES                  MEDICAL
    |employee|ID|             |ID|DateOfLeave|
         A     1                1   2019/1/3
         B     2                1   2019/4/15
         C     3                2   2019/5/16
         D     4
  

Инструкция sql, которую я придумал, фильтрует конкретный месяц отпуска и подсчитывает, сколько раз они брали отпуск в этом месяце, например, в январе, она также включает сотрудников, у которых нет никакого отпуска, как ‘0’, однако сотрудники, у которых есть отпуска по болезни, которые не приходятся на январь, вообще не отображаются в результирующем наборе, как я могу показать, что у них 0 отпусков по болезни в январе месяце?

 select employees.employee, employees.ID,
 count(medical.DateOfLeave) as NumberOfLeaves
from employees
left outer join medical on employees.ID = medical.ID
where (MONTH(DateOfLeave) = 1) or (medical.DateOfLeave is null)
group by employees.employee,employees.ID
  

            RESULT SET
    |Employee|ID|NumberOfLeaves|
        A      1      1
        C      3      0   
        D      4      0
  

Как вы можете видеть, B исчезает, но я хочу, чтобы оно отображалось в результирующем наборе как ‘0’, например, employee C и D

Я знаю это потому, что медицинские данные сотрудника B не соответствуют условию предложения where, но как мне написать инструкцию, которая включает сотрудников, имеющих отпуска по болезни, которые не приходятся на январь, в результирующий набор как 0??

Ваш запрос показывает результаты только для сотрудников, у которых отпуск в январе или которые вообще не имеют отпуска.

Вместо этого вы хотите присоединяться к записям в вашей медицинской таблице только в том случае, если есть записи за указанный месяц, тогда вы сгруппируете свои результаты и получите подсчеты оттуда.

Чтобы сделать это, вам нужно изменить условие для вашего объединения, чтобы включить фильтр месяца.

Вот рабочий пример использования табличных переменных

 DECLARE @Employees TABLE (ID INT, Employee CHAR(1))
DECLARE @Medical TABLE (ID INT, DateOfLeave DATE)

INSERT INTO @Employees
VALUES (1, 'A'), (2,'B'), (3,'C'), (4, 'D')

INSERT INTO @Medical 
VALUES (1, '2019-01-03'), (1, '2019-04-15'), (2, '2019-05-16')

SELECT          e.employee, 
                e.ID,
                count(m.DateOfLeave) AS NumberOfLeaves
FROM            @Employees e
LEFT OUTER JOIN @Medical m ON   e.ID = m.ID AND MONTH(DateOfLeave) = 1
GROUP BY        e.employee,e.ID
  

левое внешнее соединение сначала объединит таблицу, а затем отфильтрует. в вашем случае B не учитывается в самом объединении, поэтому фильтр не работает. вы можете попробовать это

 select employees.employee, employees.ID,
 nvl(count(medical.DateOfLeave),0) as NumberOfLeaves
from employees
left outer join (select * from medical 
where (MONTH(DateOfLeave) = 1)) 
on employees.ID = medical.ID
group by employees.employee,employees.ID
  

или вы также можете попробовать это

    select e.id, employee, count(dateofleave)
from employee e, medical m
where e.id = m.id
and month(m.DateOfLeave) = 1
group by e.id, employee
UNION ALL
select id, employee, 0
from employee e
where not exists(select 1 from medical m
where e.id = m.id
and month(m.DateOfLeave) = 1