How to get the first or last row in ordered result set, depending by column value?

Question

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.

Answer 1

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

Answer 2

try this

;WITH cte AS (
  SELECT
    t.EmpID,
    t.Name,
    t.PunchDate,
    t.PunchTime,
    t.DeviceType,
    ROW_NUMBER() OVER (PARTITION BY t.EmpID ORDER BY t.PunchDate, t.PunchTime) rw
    FROM test.dbo.Table_1 t  
  )


  ,cte2 AS (
  SELECT
    t.EmpID,
    t.Name,
    t.PunchDate InPunchDate,
    t.PunchTime InPunchTime,
    t2.PunchDate OutPunchDate,
    t2.PunchTime OutPunchTime
  FROM cte t
  JOIN cte t2 ON t.EmpID = t2.EmpID AND t.rw = t2.rw-1 AND t.DeviceType != t2.DeviceType 
  WHERE t.DeviceType != 'OUT'
  )

  SELECT * FROM (
    SELECT
        EmpID,
        Name,
        InPunchDate PunchDate,
        InPunchTime PunchTime,
        'IN' Device
        from cte2
    UNION ALL
    SELECT
        EmpID,
        Name,
        OutPunchDate PunchDate,
        OutPunchTime PunchTime,
        'OUT' Device
    FROM cte2
  ) AS t1
  ORDER BY EmpID, PunchDate, PunchTime

Answer 3

Well, I think this is a good example when SET operations might not be the best tool in the box. I'd go for a simple cursor here! :) Both easier to read, maintain and scans the table only once!

An RDBMS does not really have the concept of 'ordered table' unless you explicitly specify it with an ORDER BY. I do, however, guess your table is 'ordered' by < EMPID | Name , PunchDate, PunchTime>. If not, and the visualised order above is just a coincidence, I have used an additional column called RowNum.

CREATE TABLE [dbo].[OrderedData]([RowNum] [TINYINT] NOT NULL PRIMARY KEY,[EMPLID] [TINYINT] NOT NULL,   [Name] [CHAR](2) NOT NULL,[PUNCHDATE] [DATE] NOT NULL,  [PUNCHTIME] [TIME](0) NOT NULL, [DEVICETYPE] [VARCHAR](3) NOT NULL)

The data:

INSERT [dbo].[OrderedData] ([RowNum], [EMPLID], [Name], [PUNCHDATE], [PUNCHTIME], [DEVICETYPE]) 
VALUES 
(1, 70, 'GT', CAST(N'2014-05-28' AS Date), CAST(N'07:46:53' AS Time), N'IN'),
(2, 70, 'GT', CAST(N'2014-05-28' AS Date), CAST(N'07:46:58' AS Time), N'IN'),
(3, 70, 'GT', CAST(N'2014-05-28' AS Date), CAST(N'07:47:50' AS Time), N'IN'),
(4, 70, 'GT', CAST(N'2014-05-28' AS Date), CAST(N'12:16:19' AS Time), N'OUT'),
(5, 70, 'GT', CAST(N'2014-05-28' AS Date), CAST(N'12:17:55' AS Time), N'OUT'),
(6, 70, 'GT', CAST(N'2014-05-28' AS Date), CAST(N'12:24:27' AS Time), N'IN'),
(7, 70, 'GT', CAST(N'2014-05-28' AS Date), CAST(N'16:32:57' AS Time), N'OUT'),
(8, 70, 'GT', CAST(N'2014-05-28' AS Date), CAST(N'16:33:57' AS Time), N'OUT'),
(9, 70, 'GT', CAST(N'2014-05-28' AS Date), CAST(N'16:33:59' AS Time), N'OUT'),
(10, 72, 'MS', CAST(N'2014-05-28' AS Date), CAST(N'07:47:50' AS Time), N'IN'),
(11, 72, 'MS', CAST(N'2014-05-28' AS Date), CAST(N'12:16:19' AS Time), N'OUT'),
(12, 88, 'ZE', CAST(N'2014-05-28' AS Date), CAST(N'07:47:50' AS Time), N'IN'),
(13, 88, 'ZE', CAST(N'2014-05-28' AS Date), CAST(N'07:48:40' AS Time), N'IN'),
(14, 88, 'ZE', CAST(N'2014-05-28' AS Date), CAST(N'12:16:19' AS Time), N'OUT')

And my solution:

DECLARE @RowNum TINYINT, @DEVICETYPE VARCHAR(3), @LastType VARCHAR(3) = 'IN'
DECLARE @ReturnRows TABLE(RowNum TINYINT NOT NULL)
DECLARE myCur CURSOR LOCAL FAST_FORWARD  FOR
   SELECT RowNum, DEVICETYPE FROM [dbo].OrderedData ORDER BY RowNum ASC

OPEN myCur

FETCH NEXT FROM myCur INTO @RowNum , @DEVICETYPE
INSERT INTO @ReturnRows VALUES(1)

WHILE @@FETCH_STATUS = 0
BEGIN
   IF @LastType = 'OUT' AND @DEVICETYPE = 'IN'
   BEGIN
      INSERT INTO @ReturnRows VALUES(@RowNum -1)
      INSERT INTO @ReturnRows VALUES(@RowNum)
   END
   SET @LastType = @DEVICETYPE

   FETCH NEXT FROM myCur INTO @RowNum , @DEVICETYPE
END

INSERT INTO @ReturnRows VALUES(@RowNum)

CLOSE myCur
DEALLOCATE myCur

SELECT * FROM [dbo].OrderedData WHERE RowNum IN (SELECT RowNum FROM @ReturnRows) ORDER BY RowNum