-2

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Tan Nguyen
  • 21
  • 6
  • How is this different from [your previous question](https://stackoverflow.com/questions/60991568/time-attendances-sql-query)? – GMB Apr 02 '20 at 17:36
  • And https://stackoverflow.com/questions/49236088/time-attendances-query-in-microsoft-access – jarlh Apr 02 '20 at 18:15
  • @GMB, my previous question was unfortunately closed. The query unfortunatelz did not work, therefore i had to reopen it and added some more info. – Tan Nguyen Apr 02 '20 at 18:17
  • @Jarlh, this question was from a year ago, when i was still using MS Access. I have switched to SQL now. The query that worked from in Access isn't going to work in SQL. – Tan Nguyen Apr 02 '20 at 18:19
  • Add a tag for the dbms you're using now. Remember that SQL is a language supported by many different products. – jarlh Apr 02 '20 at 18:20
  • 1
    @jarlh, I have edited it. – Tan Nguyen Apr 02 '20 at 18:22
  • Can you explain why in the 2nd row of your expected results checkout is null? – forpas Apr 02 '20 at 18:27
  • @forpas, screenshot was fault, my apologies. I have edited it. – Tan Nguyen Apr 02 '20 at 19:20

1 Answers1

0

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786