SQL Query How To Get Time Difference Beetwen Two Results

Question

I have this T-SQL query:

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

And the result looks like below:

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   | 

How do I get time difference from that result? because of that query have two results each employee. Thanks

Answer 1

You can try below - using conditional aggregation and datediff() function

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