I have a table in SQL Server about how people going in and out of building.

I need to calculate how much time each user spent inside the building by days. For example, on 27th Sep user #1 spent 3 hours 5 minutes. User #2 spent 2 hours 30 minutes. There is also a bug that may spoil the results - sometimes I may have two 'in' or two 'out' in a row, like in case of user #3. I understand the nature of such bug, and know I only have to keep last of two same rows (in fact user #3 entered in 11:46, not 11:45). Does anyone have an idea how to solve that?

CodePudding user response：

select    user_id
         ,sum(time_spent) as time_spent_minutes
from     (
         select *
                ,datediff(minute, lag(case when direction = 'in' then datetime end) over(partition by user_id order by datetime), datetime) as time_spent
         from t 
         ) t
group by user_id

Fiddle

CodePudding user response：

The window functions would be a nice fit here.

Example or Updated dbFiddle

Select user_id
      ,Duration = convert(time(0),dateadd(second,sum(Secs),0))
 From (
        Select user_id
               ,Secs = datediff(second,case when direction ='in' 
                                             and lead([direction],1) over (partition by user_id order by datetime)='out' 
                                       then [datetime] 
                                       end
                                       ,lead([datetime],1) over (partition by user_id order by datetime))
         From YourTable
      ) A
 Group By user_id

Results

user_id     Duration
1           03:05:00   -- << Check your desired results
2           02:30:00
3           06:07:00