SQL最后一次超时（Transact-SQL..preferrably）

I have the following table which contains all the time in and time out of people:

我有下表，其中包含人们的所有时间和时间：

 CREATE TABLE test (
       timecardid INT
     , trandate DATE
     , employeeid INT
     , trantime TIME
     , Trantype VARCHAR(1)
     , Projcode VARCHAR(3)
)

The task is to get all the earliest trantime with trantype A (perhaps using MIN) and the latest trantime with trantype Z (Using Max), all of which in that trandate (ie. trantype A for july 17 is 8:00 AM and trantype Z for july 17 is 7:00PM).

任务是获得所有最早的trantime与trantype A（可能使用MIN）和最新trantime与trantype Z（使用Max），所有这些在该trandate（即7月17日的trantype A是8:00 AM和trantype 7月17日的Z是晚上7点）。

the problem is, the output should be in the same format as the table where it's coming from, meaning that I have to leave this data and filter out the rest (that aren't the earliest and latest in/out for that date, per employee)

问题是，输出应该与它来自的表格格式相同，这意味着我必须保留这些数据并过滤掉其余的数据（这不是该日期最早和最新的输入/输出，每个雇员）

My current solution is to use two different select commands to get all earliest, then get all the latest. then combine them both.

我目前的解决方案是使用两个不同的选择命令来获得最早的，然后获取所有最新的。然后将它们结合起来。

I was wondering though, is there a much simpler, single string solution?

我想知道，有一个更简单的单字符串解决方案吗？

Thank you very much.

非常感谢你。

EDIT (I apologize, here is the sample. Server is SQL Server 2008):

编辑（我道歉，这是示例。服务器是SQL Server 2008）：

 Timecardid | Trandate | employeeid | trantime | trantype | Projcode 
      1      2013-04-01      1        8:00:00        A       SAMPLE1
      2      2013-04-01      1        9:00:00        A       SAMPLE1
      3      2013-04-01      2        7:00:00        A       SAMPLE1
      4      2013-04-01      2        6:59:59        A       SAMPLE1
      5      2013-04-01      1       17:00:00        Z       SAMPLE1
      6      2013-04-01      1       17:19:00        Z       SAMPLE1
      7      2013-04-01      2        17:00:00       Z       SAMPLE1

      8      2013-04-02      1        8:00:00        A       SAMPLE1
      9      2013-04-02      1        9:00:00        A       SAMPLE1
     10      2013-04-02      2        7:00:58        A       SAMPLE1
     11      2013-04-02      2       18:00:00        Z       SAMPLE1
     12      2013-04-02      2       18:00:01        Z       SAMPLE1
     13      2013-04-02      1       20:00:00        Z       SAMPLE1

Expected Results (the earliest in and the latest out per day, per employee, in a select command):

预期结果（每个员工中最早的和最新的，每个员工，在一个选择命令中）：

  Timecardid | Trandate | employeeid | trantime | trantype | Projcode 
      1      2013-04-01      1        8:00:00        A       SAMPLE1 
      4      2013-04-01      2        6:59:59        A       SAMPLE1   
      6      2013-04-01      1       17:19:00        Z       SAMPLE1
      7      2013-04-01      2        17:00:00       Z       SAMPLE1
      8      2013-04-02      1        8:00:00        A       SAMPLE1
     10      2013-04-02      2        7:00:58        A       SAMPLE1
     12      2013-04-02      2       18:00:01        Z       SAMPLE1
     13      2013-04-02      1       20:00:00        Z       SAMPLE1

Thank you very much

非常感谢你

4 个解决方案

#1

Perhaps this is what you're looking for:

也许这就是你要找的东西：

select 
    t.* 
from 
    test t
where 
    trantime in (
        (select min(trantime) from test t1 where t1.trandate = t.trandate and trantype = 'A'),
        (select max(trantime) from test t2 where t2.trandate = t.trandate and trantype = 'Z')
    )

Changing my answer to account for the "per employee" requirement:

将我的答案更改为“每位员工”要求的帐户：

;WITH EarliestIn AS
(
    SELECT trandate, employeeid, min(trantime) AS EarliestTimeIn
    FROM test 
    WHERE trantype = 'A'
    GROUP BY trandate, employeeid 
), 
LatestOut AS
(
    SELECT trandate, employeeid, max(trantime) AS LatestTimeOut
    FROM test 
    WHERE trantype = 'Z'
    GROUP BY trandate, employeeid 
)
SELECT * 
FROM test t
WHERE 
    EXISTS (SELECT * FROM EarliestIn WHERE t.trandate = EarliestIn.trandate AND t.employeeid = EarliestIn.employeeid AND t.trantime = EarliestIn.EarliestTimeIn) 
    OR EXISTS (SELECT * FROM LatestOut WHERE t.trandate = LatestOut.trandate AND t.employeeid = LatestOut.employeeid AND t.trantime = LatestOut.LatestTimeOut)

#2

Assuming timecardid column is PK or unique, and if I understand it correctly, I would do something like

假设timecardid列是PK或唯一的，如果我理解正确，我会做类似的事情

DECLARE @date DATE
SET @date = '2013-07-01'

SELECT
    T0.*
FROM
    (SELECT DISTINCT employeeid FROM test) E
    CROSS APPLY (
        SELECT TOP 1
            T.timecardid
        FROM
            test T
        WHERE
            T.trandate = @date
            AND T.Trantype = 'A'
            AND T.employeeid = E.employeeid
        ORDER BY T.trantime
        UNION ALL
        SELECT TOP 1
            T.timecardid
        FROM
            test T
        WHERE
            T.trandate = @date
            AND T.Trantype = 'Z'
            AND T.employeeid = E.employeeid
        ORDER BY T.trantime DESC
    ) V
    JOIN test T0 ON T0.timecardid = V.timecardid

Appropriate indexes should be set for the table, if you aware of performance.

如果您了解性能，则应为表设置适当的索引。

#3

If you're using SQL server 2012, you can use LAG/LEAD to find the max and min rows in a fairly concise way;

如果您使用的是SQL Server 2012，则可以使用LAG / LEAD以相当简洁的方式查找最大行数和最小行数;

WITH cte AS (
  SELECT *,
       LAG(timecardid)  OVER (PARTITION BY trandate,employeeid,trantype ORDER BY trantime)  lagid,
       LEAD(timecardid) OVER (PARTITION BY trandate,employeeid,trantype ORDER BY trantime) leadid
  FROM test
)
SELECT timecardid,trandate,employeeid,trantime,trantype,projcode
FROM cte
WHERE trantype='A' AND lagid  IS NULL
   OR trantype='Z' AND leadid IS NULL;

An SQLfiddle to test with.

一个要测试的SQLfiddle。

#4

I would use ROW_NUMBER to sort out the rows you want to select:

我会使用ROW_NUMBER来排序您要选择的行：

;with Ordered as (
  select *,
    ROW_NUMBER() OVER (PARTITION BY Trandate,employeeid,trantype
        ORDER BY trantime ASC) as rnEarly,
    ROW_NUMBER() OVER (PARTITION BY Trandate,employeeid,trantype
        ORDER BY trantime DESC) as rnLate
  from
    Test
)
select * from Ordered
where
  (rnEarly = 1 and trantype='A') or
  (rnLate = 1 and trantype='Z')
order by TimecardId

(SQLFiddle)

（SQLFiddle）

It produces the results you've requested, and I think it's quite readable. The reason that trantype is included in the PARTITION BY clauses is so that A and Z values receive separate numbering.

它会产生您要求的结果，我认为它非常易读。 trantype包含在PARTITION BY子句中的原因是A和Z值接收单独的编号。

#1