sql - Get All punch in and out for each employee?

Question

Expected Output:

SL#  Emp#   Employee Name       IN                     OUT         
1    106    George Jacob 24/Sep/2017 8:08:00AM 24/Sep/2017 12:53:00PM 04:45:00
                         24/Sep/2017 2:13:00PM 24/Sep/2017 5:58:00PM 03:45:00
                         25/Sep/2017 8:12:00AM 25/Sep/2017 6:02:00PM 09:50:00
                         26/Sep/2017 8:18:00AM 26/Sep/2017 2:15:00PM 05:57:00
                         26/Sep/2017 2:32:00PM 26/Sep/2017 6:00:00PM 03:28:00
                         27/Sep/2017 8:02:00AM 27/Sep/2017 5:57:00PM 09:55:00
                         28/Sep/2017 8:01:00AM 28/Sep/2017 6:01:00PM 10:00:00
                         01/Oct/2017 8:16:00AM 01/Oct/2017 5:56:00PM 09:40:00
                         02/Oct/2017 7:58:00AM 02/Oct/2017 5:56:00PM

I tried this query but not get an exact output as mentioned above:

SELECT Row_number()
     OVER (ORDER BY A.dt ASC)                        AS SNo,
     CONVERT(DATE, A.dt)
   --CONVERT(VARCHAR(26), A.DT, 103)  as DATEEVENT, 
                                                 b.emp_code,
   B.emp_name,
   F.event_entry_name,
   a.dt,
   Cast(LEFT(CONVERT(TIME, a.dt), 5) AS VARCHAR) AS 'time',
   Isnull(B.areaname, 'OAE6080036073000006')     AS areaname,
   C.dept_name,
   b.emp_reader_id,
   Isnull(c.dept_name, '')                       AS group_name,
   CONVERT(CHAR(11), '2017/12/30', 103)          AS StartDate,
   CONVERT(CHAR(11), '2018/01/11', 103)          AS ToDate,
   0                                             AS emp_card_no
FROM   dbo.trnevents AS A
   LEFT OUTER JOIN dbo.employee AS B
                ON A.emp_reader_id = B.emp_reader_id
   LEFT OUTER JOIN dbo.departments AS C
                ON B.dept_id = C.dept_id
   LEFT OUTER JOIN dbo.devicepersonnelarea AS E
                ON A.pointid = E.areaid
   LEFT OUTER JOIN dbo.event_entry AS F
                ON A.eventid = F.event_entry_id  

Answer 1

You could use a subselect or CTE to get the data ordered by employee and the use it as the main data table. Something similar (adapt it if necessary) to this:

;with ordered as (
select 
    emp_reader_id as empId,
    CONVERT(DATE, dt) as Punch,
    Row_number()
     OVER (PARTITION BY emp_reader_id ORDER BY CONVERT(DATE, dt) ASC) as OrderedPunch
from trnevents
)
SELECT 
    entered.empId, 
    entered.Punch as PunchIn,
    exited.Punch as PunchOut
from
    ordered as entered
    left join ordered as exited on 
        entered.empId = exited.empId
        and entered.OrderedPunch + 1 = exited.OrderedPunch

Explanation: The 'ordered' CTE does show the employee entry/exits ordered by date. The ROW_NUMBER is reset for each employee (I assume the emp_reader_id does contains the employee id) because of the PARTITION BY.

Once I got the counter for each employee, I join each punch for each employee (first condition in the left join) with the next punch for that employee (second condition in the left join). That way I can show the entry column and the exit (the next punch).

After you got the in and out columns in your data you may want to exclude some data (the odd rows of each employee are the rows you would want) adding WHERE entered.OrderedPunch %2 = 1