How to get the first or last row in ordered result set, depending by column value? I have the follwoing result set from the table PUNCHREPORT

EMPLID   Name    PUNCHDATE    PUNCHTIME             DEVICETYPE
..................................................................

70       GT     2014-05-28    07:46:53.0000000      IN          
70       GT     2014-05-28    07:46:58.0000000      IN  
70       GT     2014-05-28    07:47:50.0000000      IN          
70       GT     2014-05-28    12:16:19.0000000      OUT   
70       GT     2014-05-28    12:17:55.0000000      OUT         
70       GT     2014-05-28    12:24:27.0000000      IN          
70       GT     2014-05-28    16:32:57.0000000      OUT
70       GT     2014-05-28    16:33:57.0000000      OUT
70       GT     2014-05-28    16:33:59.0000000      OUT
72       MS     2014-05-28    07:47:50.0000000      IN          
72       MS     2014-05-28    12:16:19.0000000      OUT 
88       ZE     2014-05-28    07:47:50.0000000      IN          
88       ZE     2014-05-28    07:48:40.0000000      IN          
88       ZE     2014-05-28    12:16:19.0000000      OUT 


I need the above should be result as follow:

EMPLID   Name    PUNCHDATE    PUNCHTIME             DEVICETYPE
..................................................................

70       GT     2014-05-28    07:46:53.0000000      IN          
70       GT     2014-05-28    12:17:55.0000000      OUT         
70       GT     2014-05-28    12:24:27.0000000      IN          
70       GT     2014-05-28    16:33:59.0000000      OUT
72       MS     2014-05-28    07:47:50.0000000      IN          
72       MS     2014-05-28    12:16:19.0000000      OUT         
88       ZE     2014-05-28    07:47:50.0000000      IN          
88       ZE     2014-05-28    12:16:19.0000000      OUT 


Means for every occurence of DEVICETYPE IN the first row will be taken and for OUT  the last row will be taken.
Doing that is tricky, because you need to check the differences between the data in both orders, ascending to get the first 'IN', descending to get the last 'OUT'.

With I AS (
  SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
       , ID_Up = Row_Number() OVER (PARTITION BY EMPLID 
                                    ORDER BY EMPLID, PUNCHDATE, PUNCHTIME)
       , ID_Down = Row_Number() OVER (PARTITION BY EMPLID 
                                      ORDER BY EMPLID, PUNCHDATE DESC, PUNCHTIME DESC)
  FROM   Table1
), S AS (
  SELECT a.EMPLID, a.Name, a.PUNCHDATE, a.PUNCHTIME, a.DEVICETYPE
       , a.ID_Up aUID, b.ID_Up bUID, a.ID_Down aDID, c.ID_Down cDID
       , a.DeviceType aDeviceType, b.DeviceType bDeviceType, c.DeviceType cDeviceType
       , OKValues 
       = CASE WHEN a.ID_Up = 1 THEN 1
              WHEN a.DeviceType = 'IN' AND b.DeviceType = 'OUT' THEN 1
              WHEN a.ID_Down = 1 THEN 1
              WHEN a.DeviceType = 'OUT' AND c.DeviceType = 'IN' THEN 1
              ELSE 0
         END
  FROM   I a
         LEFT  JOIN I b ON a.EmplID = b.EmplID And b.ID_Up = a.ID_Up - 1
         LEFT  JOIN I c ON a.EmplID = c.EmplID And c.ID_Down = a.ID_Down - 1
)
SELECT EMPLID, Name, PUNCHDATE, PUNCHTIME, DEVICETYPE
FROM   S
WHERE  OKValues = 1
ORDER BY EMPLID, PUNCHDATE, PUNCHTIME


SQLFiddle

the S CTE is to remove the CASE from the WHERE condition, in the CTE I left a lot of noise data, that can be useful to understand the CASE conditions