I am having staff's movement data in table AttendanceReg like:

I want a report of Employees with their 1st In and last out with it's place. (should depends In and Out time, not the place)

when I tried aggregate function and group by, it returned:

Alter PROCEDURE UDP_AttReport @SelectDt Date
AS
select EmpID as ID,max(InPlace) as InPlace,min(InTime) as InTime Into #AttIN from AttendanceReg where cast(InTime as Date) = cast(@SelectDt as date) group by EmpID order by InTime desc 
select EmpID as ID, max(InPlace)as OutPlace,max(OutTime) as OutTime Into #AttOut from AttendanceReg where cast(InTime as Date) = cast(@SelectDt as date) group by EmpID order by OutTime desc 

SELECT #AttIN.ID,InPlace,InTime,OutPlace,OutTime Into #AttRep FROM #AttIN
RIGHT JOIN #AttOut
ON #AttIN.ID = #AttOut.ID
select ID,InPlace,InTime,OutPlace,OutTime from #AttRep Order by InTime asc

drop table #AttIN
drop table #AttOut
drop table #AttRep

Go

but what I expect is

I can understand the problem is with using Max(InPlace) (I learnt it somewhere to trick SQL to return the column without grouping by that column).

Thanks.

CodePudding user response：

Try this :

Select  a.EmpId, 
        a.InPlace, 
        a.InTime,
        b.Inplace as Outplace , 
        b.OutTime
From
    (Select EmpId, InPlace, InTime, ROW_NUMBER() over(partition by EmpId order by InTime) rn From test) a
inner join 
    (Select EmpId, InPlace, OutTime, ROW_NUMBER() over(partition by EmpId order by OutTime desc) rn From test) b
on 
    a.EmpId = b.EmpId
Where 
   a.rn = 1 
   and b.rn = 1

Output

CodePudding user response：

use window function row_number() to find the first and last In or Out by EmpId

with cte as
(
    select *, 
           rn = row_number() over (partition by EmpId order by InTime),
           rc = count(*) over (partition by EmpId)
    from   AttendanceReg
)
select EmpId, 
       InPlace  = max(case when rn = 1 then InPlace end),
       InTime   = max(case when rn = 1 then InTime end),
       OutPlace = max(case when rn = rn then InPlace end),
       OutTime  = max(case when rn = rn then OutTime end)
from   cte
group by EmpId

Is there an error on the OutTime for 003? it is supposed to be 08PM?