Search code examples
sqlsql-servertime-and-attendance

SQL Time Attendance Query


Recently I made a switch from MS Access to SQL Server. Due to this switch I am having issues with making one SQL query to work.

This is how the current table looks like in SQL.

enter image description here

This is what I am trying to get as result from the query:

enter image description here

Previously I was able to make it work in MS Access with the following query:

SELECT m.UserEnrollNumber, m.Checktime AS TimeIn, (SELECT Min(s.Checktime)
    FROM CheckInOut1 s
    WHERE s.UserEnrollNumber = m.UserEnrollNumber
    AND s.Checktime > m.Checktime
    AND s.Checktime <= Int(m.Checktime) + 1) AS TimeOut
FROM CheckInOut1 AS m
WHERE ((((SELECT COUNT(*) 
    FROM CheckInOut1 s 
    WHERE s.UserEnrollNumber = m.UserEnrollNumber
    AND s.Checktime <= m.Checktime
    AND s.Checktime >= INT(m.Checktime)) Mod 2)=1));

The following query as answer from @GMB:

select
        employee_id,
        min(time_in_out) check_in,
        max(time_in_out) check_out
    from (
        select t.*, row_number() over(partition by employee_id order by time_in_out) - 1 rn
        from mytable t
    ) t
    group by employee_id, floor(rn / 2) 
    order by employee_id, floor(rn / 2)

from SQL table:

enter image description here

gives me the following result:

enter image description here

Seems like the minimum and maximum rows are shown, but the rows in between are not.

The following query from @Gordon Linoff:

SELECT cio.EmployeeID, cio.TimeInOut AS CheckIn, 
       cio.TimeInOut as CheckOut
FROM (SELECT cio.*,
             ROW_NUMBER() OVER (PARTITION BY cio.EmployeeID, CONVERT(date, cio.TimeInOut) ORDER BY cio.TimeInOut) as seqnum,
             LEAD(cio.TimeInOut) OVER (PARTITION BY cio.EmployeeID, CONVERT(date, cio.TimeInOut) ORDER BY cio.TimeInOut) as next_TimeInOut
      FROM CheckInOut22 cio
     ) cio
WHERE seqnum % 2 = 1;

Gives me the following result:

enter image description here

Checkin is the same as CheckOut.

All help would be appreciated.


Solution

  • This is much simpler in SQL Server. Use window functions:

    SELECT cio.EmployeeID, cio.TimeInOut AS CheckIn, 
           cio.next_TimeInOut as CheckOut
    FROM (SELECT cio.*,
                 ROW_NUMBER() OVER (PARTITION BY cio.EmployeeID, CONVERT(date, cio.TimeInOut) ORDER BY cio.TimeInOut) as seqnum,
                 LEAD(cio.TimeInOut) OVER (PARTITION BY cio.EmployeeID, CONVERT(date, cio.TimeInOut) ORDER BY cio.TimeInOut) as next_TimeInOut
          FROM CheckInOut cio
         ) cio
    WHERE seqnum % 2 = 1;