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   | 

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

- Roman

---

你期望的输出是什么？ - PSK

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

- Fahmi

Answer 2

尝试像这样旋转值：

;WITH CTE
AS
(
    SELECT 
        * 
        FROM   
        (
            SELECT 
                a.Id, 
                u.[Name], 
                Cast(a.[Date] AS DATE)      AS [Date], 
                Min(Cast(a.Timex 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],
    [Location],
    TimeIn = DATEDIFF(hh,[In],[Out])
    FROM CTE
    PIVOT
    (
        MAX(Timex)
        FOR
        [State] IN 
        (
            [In],[Out]
        )   
    )Q

- Jayasurya Satheesh

---

运行完美。非常感谢。 - Roman