CODEWITHSUNDEEP
sqlsql-server
ebe nazer
ebe nazer

Reputation: 44

How to find out Firstin LastOut of Night Shift Employee in SQL Server

I can get FirstIn - LastOut out of Day shift employee using min(),max(). But i have problem for night shift employee like 5:30 Pm to next day Morning 2:30 am. At the same day(Next day) they will come again on 5:30 pm. So I could not able to calculate..

Ex: Shift Table

  UserID     ShiftName    Start   End     
   2267     Night Shift-1  17:30  02:30

Ex: Employee Table :

   UserID     LogIn                   LogOut                         LogDate
    2267   2016-01-04 20:52:08.000  2016-01-04 22:09:22.000   2016-01-04 00:00:00.000
    2267   2016-01-04 23:00:07.000  2016-01-04 23:00:07.000   2016-01-04 00:00:00.000
    2267   2016-01-05 00:35:46.000  2016-01-05 00:35:46.000   2016-01-05 00:00:00.000
    2267   2016-01-05 01:02:31.000  2016-01-05 03:57:16.000   2016-01-05 00:00:00.000
    2267   2016-01-05 18:43:50.000  2016-01-05 19:05:04.000   2016-01-05 00:00:00.000
    2267   2016-01-05 19:10:20.000  2016-01-05 22:26:00.000   2016-01-05 00:00:00.000
    2267   2016-01-05 23:27:24.000  2016-01-05 23:27:24.000   2016-01-05 00:00:00.000
    2267   2016-01-06 03:45:16.000  2016-01-06 03:45:16.000   2016-01-06 00:00:00.000

I want output :

        EmpId     date           FirstIN    LastOUT
        2267    2016-01-04        20:52      03:57
        2267    2016-01-05        18:43      03:45

Upvotes: 1

Views: 443

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

I think this will work better with lead(). Get the "out" date on the same row and then do the aggregation:

select userid, cast(logdate as date) as thedate,
       min(logdate), max(next_logdate)
from (select l.*,
             lead(logdate) over (partition by userid order by logdate) as next_logdate
      from logintable l
     ) l
where l.details = 'In'
group by userid, cast(logdate as date)
order by userid, cast(logdate as date);

(This isn't doing the conversion back to time to focus on the important part of the logic.)

Note: this assumes the the next log record for an 'In' record is always an 'Out' record. This is true of the data in your question.

Upvotes: 1

Related Questions