你可以试试像下面这样的查询

you can try a query like below

查看工作演示

create table staff(StaffId int,   FirstName  nvarchar(10),  LastName nvarchar(10),  staffType nvarchar(10))
insert into staff values
(1,'Adam','Sorme','Student') 
,(2,'Lara','Sandra','Teacher')
,(3,'Jack','Jones','Student')

go

create table gate(GateId int,  GateName  nvarchar(10))
insert into gate values
(1,'frontDoor')
,(2,'superDoor')

go
create table logs 
(Id int, transitionDate  datetime,   GateId  int, StaffId  int)
insert into logs values
(1,'2018-01-1 08:00:00',1,1)
,(2,'2018-01-1 10:00:00',2,1)
,(3,'2018-01-1 20:00:00',2,1)
,(4,'2018-01-2 07:00:00',1,2)
,(5,'2018-01-2 10:00:00',1,3)
,(6,'2018-01-9 12:00:00',2,2)
go
declare @startdate datetime, @enddate datetime
select @startdate='2018-01-1 00:00:00' , @enddate='2018-01-03 00:00:00'


; with tempSet as 
(
    select 
    transitionDatetime=l.transitionDate,
    gateName=g.gateName,
    staffid=l.staffid,
    idx=
         row_number() over(partition by l.staffid order by l.transitionDate ) -
        row_number() over(partition by l.staffid,cast(l.transitionDate as date) order by l.transitionDate ),
    transitionDate=cast(l.transitionDate as date)
    from
    logs l inner join staff s on
    l.staffid=s.staffid and staffType='Student'
    join gate g on g.gateid=l.gateid
)
, groupedSet as
(
    select 
    t1.*,
    FirstGateName=t2.gatename,
    lastGateName=t3.gatename
    from
    (
        select
        staffid,
        mintransitionDate=min(transitionDatetime),
        maxtransitionDate= case when count(1)>1 then max(transitionDatetime) else null end,
        transitionDate=max(transitionDate),
        idx
        from
        tempSet 
        group by staffid,idx
    ) t1
    left join
    tempSet t2
    on t1.idx=t2.idx 
    and t1.staffid=t2.staffid and t1.mintransitionDate=t2.transitionDatetime
     left join
    tempSet t3
    on t1.idx=t3.idx 
    and t1.staffid=t3.staffid and t1.maxtransitionDate=t3.transitionDatetime
    where t1.transitionDate between @startdate and @enddate
 )

 select
 t.*,
 g.mintransitionDate,
 g.maxtransitionDate,
 g.FirstGateName,
 g.LastGateName
 from 
 groupedSet g
 right join
 (
     select 
         d,
         staffid
     from
     (
         select 
         top  (select datediff(d,@startdate, @endDate))
         d=dateadd(d,row_number() over(order by (select null))-1, @startDate)
         from
         sys.objects o1 cross join sys.objects o2
     )tally
          cross join
     staff 
     where staff.stafftype='Student'
    )t
 on cast(t.d as date)=cast(g.transitionDate as date) and t.staffid=g.staffid
 order by t.d asc, t.staffid asc