CODEWITHSUNDEEP
sql-server-2008stored-procedures
Kishan Gajjar
Kishan Gajjar

Reputation: 1138

Need help in sql query

I have following entries in my database table InOut

AutoId EmployeeId LogTime
1      1          12:24 PM
2      5          12:26 PM
3      1          12:27 PM
4      1          12:28 PM
5      5          12:31 PM

and so on...

No in my report i want to consider that first (Odd entry for the employee is In Time ) and Even entry for the same employee is Out Time.

In short i want following output

EmployeeId  InTime   OutTime
1           12:24 PM 12:27 PM
1           12:28 PM ---
5           12:26 PM 12:31 PM

so how can i achieve this output ??

Upvotes: 3

Views: 111

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

This will do what you want but if there is a mismatch between login and logout events you will have the wrong result. You should have some way to know if the event is a login or a logout event. 

;with cte as
( 
  select EmployeeId,
         LogTime,
         row_number() over(partition by EmployeeId order by LogTime) as rn
  from @T
)
select C1.EmployeeId,
       C1.LogTime as InTime,
       C2.LogTime as OutTime
from cte as C1
  left outer join cte as C2
    on C1.EmployeeId = C2.EmployeeId and
       C1.rn + 1 = C2.rn
where C1.rn % 2 = 1

Upvotes: 4

Related Questions