Блог

У меня есть 2 таблицы, 1 — это EMployeeMaster, а другая — посещаемость

 ***EmployeeMaster***

EmployeeId    EmployeeName   DepartmentId
1             ABC            1
2             XYZ            2
3             PQR            2
4             WXY            1
  

Теперь у меня есть другая таблица посещаемости

 ***Attendance***

AttendanceId EmployeeId      Date                  InTime       OutTime
1            1            2011-04-04 00:00:00    10:00 AM     6:30 PM
2            2            2011-04-04 00:00:00    09:45 AM     7:10 PM
  

Как только сотрудник заходит в офис и нажимает пальцем на устройство, его запись переходит в таблицу посещаемости с указанием InTime, EmployeeID и даты.

Таким образом, сотрудник, который не пришел в офис, его запись не будет существовать в таблице посещаемости.

Теперь я хочу сгенерировать ежедневный отчет..В нем должна отображаться посещаемость всех сотрудников компании вместе с их временем работы по дате. Все отсутствующие сотрудники также должны отображаться в этом отчете.

Итак, я хочу :

 EmployeeId    EMployeeName   DepartmentId  Date                 InTime    OutTime
1             ABC            1             2011-04-04 00:00:00  10:00 AM  6:30 PM
2             XYZ            2             2011-04-04 00:00:00  09:45 AM  7:10 PM
3             PQR            2             NULL/-               NULL/-    NULL/-
4             WXY            1             NULL/-               NULL/-    NULL/-
  

Можете ли вы сказать мне, каким должен быть запрос???

Вы должны использовать левое внешнее соединение.

 declare @E table (EmployeeId int,    EmployeeName varchar(50),   DepartmentId int)
declare @A table (AttendanceId int, EmployeeId int, [Date] date, InTime time, OutTime time)

insert into @E values
(1,             'ABC',            1),
(2,             'XYZ',            2),
(3,             'PQR',            2),
(4,             'WXY',            1)

insert into @A values
(1,            1,            '2011-04-04 00:00:00',    '10:00 AM',     '6:30 PM'),
(2,            2,            '2011-04-04 00:00:00',    '09:45 AM',     '7:10 PM')

select
  E.EmployeeId,
  E.EmployeeName,
  E.DepartmentId,
  A.[Date],
  A.InTime,
  A.OutTime
from @E as E
  left outer join @A as A
    on E.EmployeeId = A.EmployeeId and
       A.[Date] = '2011-04-04 00:00:00'
  

Результат

 EmployeeId  EmployeeName                                       DepartmentId Date       InTime           OutTime
----------- -------------------------------------------------- ------------ ---------- ---------------- ----------------
1           ABC                                                1            2011-04-04 10:00:00.0000000 18:30:00.0000000
2           XYZ                                                2            2011-04-04 09:45:00.0000000 19:10:00.0000000
3           PQR                                                2            NULL       NULL             NULL
4           WXY                                                1            NULL       NULL             NULL
  

Редактировать 1

Если вам нужно сделать это в течение одного месяца, я бы использовал какую-нибудь таблицу чисел или таблицу календаря. Здесь я использовал cte для построения календаря, используя @FromDate и @ToDate

 declare @E table (EmployeeId int, EmployeeName varchar(15), DepartmentId int)
declare @A table (AttendanceId int, EmployeeId int, [Date] date, InTime time, OutTime time)

insert into @E values
(1, 'ABC', 1),
(2, 'XYZ', 2),
(3, 'PQR', 2),
(4, 'WXY', 1)

insert into @A values
(1, 1, '2011-04-02', '04:00 AM', '4:30 PM'),
(2, 2, '2011-04-02', '05:00 AM', '5:30 PM'),
(3, 1, '2011-04-03', '06:00 AM', '6:30 PM'),
(4, 2, '2011-04-03', '07:00 AM', '7:30 PM'),
(5, 1, '2011-04-04', '08:00 AM', '8:30 PM'),
(6, 2, '2011-04-05', '09:00 AM', '9:10 PM')

-- Set FromDate to first day of month
declare @FromDate date = '20110401'
-- Set ToDate to last day of month
declare @ToDate date = '20110405'

-- Create cte with all dates between FromDate and ToDate
;with cteCal as 
(
  select @FromDate as [Date]
  union all
  select dateadd(d, 1, [Date]) as [Date]
  from cteCal
  where [Date] < @ToDate
)
select
  E.EmployeeId,
  E.EmployeeName,
  E.DepartmentId,
  C.[Date],
  A.InTime,
  A.OutTime
from cteCal as C
  cross join @E as E
  left outer join @A as A
    on E.EmployeeId = A.EmployeeId and
       C.[Date] = A.[Date]
order by C.[Date], E.EmployeeName 
option (maxrecursion 0)
  

Должно выглядеть так:

 select 
  EmployeeId,
  EmployeeName,
  DepartmentId,
  Date,
  InTime,
  OutTime
from EmployeeMaster em
left join Attendance a on em.EmployeeId=a.EmployeeId and Date='2011-04-04 00:00:00'
  

 select E.EmloyeeId, EmployeeName, DepartmentId, Date, InTime, OutTime
from   EmployeeMaster as e
       LEFT JOIN Attendance as a ON a.EmloyeeId = e.EmloyeeId