如何在SQL Server中获取考勤系统的第一名和最后一名

Question

我有一个表IOData，如下所示

   E Name   Date            Time    Status
   A1   08/06/14    19:36:10.00 Entry
   A1   08/06/14    19:36:16.00 Exit
   A1   08/06/14    19:36:18.00 Entry
   A1   08/07/14    00:26:39.00 Exit
   A1   08/07/14    00:38:36.00 Entry
   A1   08/07/14    02:41:14.00 Exit
   B1   08/07/14    19:12:29.00 Entry
   B1   08/08/14    04:14:28.00 Exit
   B1   08/08/14    04:14:31.00 Entry
   B1   08/08/14    04:14:33.00 Exit
   C1   08/06/14    19:26:10.00 Entry
   C1   08/06/14    19:36:16.00 Exit
   C1   08/06/14    19:36:50.00 Entry
   C1   08/06/14    23:06:28.00 Exit

我希望输出为第一个入口和最后一个出口，如下所示

 EName    Date        Time      Status
   A1    08/06/14   19:36:10.00 Entry
   A1    08/07/14   02:41:14.00 Exit
   B1    08/07/14   19:12:29.00 Entry
   B1    08/08/14   04:14:33.00 Exit
   C1    08/06/14   19:26:10.00 Entry
   C1    08/06/14   23:06:28.00 Exit

我不知道该干什么。我在谷歌上搜索过，但没有帮助。请做必要的事。

提前感谢

Answer 1

DECLARE @IOData AS TABLE(Name VARCHAR(MAX), [Date] DATE, [Time] TIME, [Status] VARCHAR(MAX)) INSERT INTO @IOData(Name, [Date], [Time], [Status]) VALUES ('A1', '08/06/14', '19:36:10.00', 'Entry'), ('A1', '08/06/14', '19:36:16.00', 'Exit'), ('A1', '08/06/14', '19:36:18.00', 'Entry'), ('A1', '08/07/14', '00:26:39.00', 'Exit'), ('A1', '08/07/14', '00:38:36.00', 'Entry'), ('A1', '08/07/14', '02:41:14.00', 'Exit'), ('B1', '08/07/14', '19:12:29.00', 'Entry'), ('B1', '08/08/14', '04:14:28.00', 'Exit'), ('B1', '08/08/14', '04:14:31.00', 'Entry'), ('B1', '08/08/14', '04:14:33.00', 'Exit'), ('C1', '08/06/14', '19:26:10.00', 'Entry'), ('C1', '08/06/14', '19:36:16.00', 'Exit'), ('C1', '08/06/14', '19:36:50.00', 'Entry'), ('C1', '08/06/14', '23:06:28.00', 'Exit')

;WITH _FirstEntries AS ( SELECT DISTINCT FirstEntries.* FROM @IOData AS ID OUTER APPLY (SELECT TOP 1 * FROM @IOData AS ID2 WHERE [Status] = 'Entry' AND ID2.Name = ID.Name ORDER BY ID2.[Date] ASC , ID2.[Time] ASC) AS FirstEntries ) , _LastExits AS( SELECT DISTINCT LastExit.* FROM @IOData AS ID OUTER APPLY (SELECT TOP 1 * FROM @IOData AS ID2 WHERE [Status] = 'Exit' AND ID2.Name = ID.Name ORDER BY ID2.[Date] DESC , ID2.[Time] DESC) AS LastExit )
SELECT * FROM ( SELECT * FROM _FirstEntries UNION ALL
SELECT * FROM _LastExits ) AS ResultUnSorted ORDER BY Name, [Status]

Answer 2

;WITH MyCTE AS
(
    SELECT EName,
           Date,
           Time,
           Status,
           ROW_NUMBER() OVER(PARTITION BY EName ORDER BY Date, Time) AS FirstRn,
           ROW_NUMBER() OVER(PARTITION BY EName ORDER BY Date DESC, Time DESC) AS LastRn
    FROM   IOData 
)
SELECT   EName,
         Date,
         Time,
         Status
FROM     MyCTE  
WHERE    FirstRn = 1 
         OR LastRn = 1
ORDER BY EName,
         Date,
         Time

SQL Fiddle DEMO