Return to Answer

* Included the Example data for ease of repro

Source Link

edited Jul 28, 2015 at 11:14

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')

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

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')

Source Link

created Jul 27, 2015 at 14:17

HansLindgren

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,[EMPLID] [TINYINT] NOT NULL,   [Name] [CHAR](2) NOT NULL,[PUNCHDATE] [DATE] NOT NULL,  [PUNCHTIME] [TIME](0) NOT NULL, [DEVICETYPE] [VARCHAR](3) NOT NULL)

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