SQL查询如何获取两个结果之间的时间差

Question

我有这个 T-SQL 查询:

select * 
from 
    (select 
         a.ID, u.Name, 
         cast(a.time as date) as Date, 
         min(cast(a.time as time)) as Timex, 
         a.location as Location, a.state as State
     from        
         ATTENDANCE a
     join           
         EMPLOYEE u on a.id = u.id COLLATE DATABASE_DEFAULT
     group by    
         a.id, u.Name, cast(a.time as date), a.location, a.state) x 
where 
    x.Date >= '2019-01-07' 
    and x.Date <= '2019-01-07' 
    and x.Location = 'Office' 
    and x.id = '1' 
order by 
    x.Name asc, x.State asc

结果如下所示:

ID | Name   | Date      | Timex  | Location | State |
---+--------+-----------+--------+----------+-------+
1  |Joe     |2019-01-07 |08:00:00| Office   | In    | 
1  |Joe     |2019-01-07 |18:00:00| Office   | Out   | 

我如何从该结果中获得时差？因为那个查询每个员工都有两个结果。谢谢

Answer 1

您可以尝试以下 - 使用条件聚合和 datediff() 函数

with cte as 
(
select * from (select a.ID, u.Name,cast(a.time as date) as Date ,min(cast(a.time as time)) as Timex, a.location as Location,a.state as State
            from        ATTENDANCE a
            join        EMPLOYEE u
            on          a.id = u.id
            COLLATE DATABASE_DEFAULT
            group by    a.id, u.Name, cast(a.time as date),a.location,a.state)x where x.Date>= '2019-01-07' and x.Date <= '2019-01-07' AND  x.Location ='Office' and x.id='1' order by x.Name ASC,x.State ASC
)
select id, name, date,
  datediff(hh,max(case when state='In' then timex end),
  max(case when state='Out' then timex end)) from cte
  group by id, name, date