提取每个人每天的第一项 [英] Extract first entry for each person for each day

问题描述

我有一张桌子，我正在尝试为每个人每天只选择第一个例外"-例如:

I have a table, and I am trying to select just the first "exception" for each day for each person - so for example:

date        agentname   exception   start   stop
01/10/2013  Smith Bob   Open Time   07:00   08:30
01/10/2013  Smith Bob   Open Time   08:45   10:45
07/10/2013  Smith Bob   Open Time   07:00   08:30
07/10/2013  Smith Bob   Open Time   08:45   10:45
08/10/2013  Smith Bob   Open Time   07:00   08:30
08/10/2013  Smith Bob   Open Time   08:45   10:45
04/10/2013  Owen John   On Loan     07:00   14:00
04/10/2013  Owen John   Break       14:00   15:00
07/10/2013  Rix Linda   Open Time   09:00   10:15
07/10/2013  Rix Linda   On Loan     11:00   11:15

sql查询将需要退出:

The sql query would need to would pull out:

date        agentname   exception   start   stop
01/10/2013  Smith Bob   Open Time   07:00   08:30
07/10/2013  Smith Bob   Open Time   07:00   08:30
08/10/2013  Smith Bob   Open Time   07:00   08:30
04/10/2013  Owen John   On Loan     07:00   14:00
07/10/2013  Rix Linda   Open Time   09:00   10:15

因此，鲍勃·史密斯(Bob Smith)-他在2013年10月10日有2条记录-1条从07:00开始，另一条从08:45开始-我只希望第一个被撤出(对于他，以及其他所有人，每天).

So take Bob Smith - he has two entries on 01/10/2013 - one starting at 07:00 and one starting at 08:45 - I just want the first one pulled out (for him, and everyone else, for each day).

任何人都可以建议，如何在Sql或Linq中执行此操作吗?

Can anyone advise please, how to do this in either Sql or Linq?

感谢您的帮助，

标记

推荐答案

您只需要对内部查询进行分组，以获取每天和座席的最短时间.

You just need to group in the inner query to get the minimum time for each day and agent.

 select [date], agentname, [exception], [start], [stop]
 from table1 t
 inner join
 (
    select [date], agentname, min([start]) as [start]
    from table1
    group by [date], agentname
 ) X
 on t.[date] = x.[date] and t.agentname = x.agentname and t.[start] = x.[start]

只要每天和座席的开始时间都是唯一的，这将起作用.

This will work provided that start times are unique for each day and agent.

这篇关于提取每个人每天的第一项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！