CODEWITHSUNDEEP
sqlsql-serverentity-frameworkt-sql
Rohit
Rohit

Reputation: 231

How to calculate time intervals between events in T-SQL

I have a table called Events. It has Computer_Id, User_Id, Event_Type and Event_DateTime. When an user logs into a computer, Events table gets an entry for that login, similarly for logout from all computers in the network. The requirement is to find how much time a computer was in use or how much time a given user spend on a computer or can be how much time all computers were in use, that is the time interval between the logins and logouts, for a given day lets day where Event_DateTime>='2018-04-26 00:00:01' and Event_DateTime>='2018-04-26 11:59:59'. The trick part is sometimes a login might not have associate logout and vice versa. I will highly appreciate your insights to approach this problem. Thank you Login = 43 and Logout = 42 enter image description here

Sample Data Link for test.bak

Upvotes: 0

Views: 817

Answers (1)

D-Shih
D-Shih

Reputation: 46239

If I understand correct,you need to use CROSS APPLYon subquery to get the most close date by logout.

like this.

 SELECT t1.Computer_ID,
         t1.User_ID,
         CONVERT(varchar, DATEADD(s, DATEDIFF(s,t1.Event_time   ,t2.Event_time), 0), 108) 'castTime' 
  FROM T1 t1 CROSS APPLY (
      SELECT TOP 1 * 
      FROM T1 t2
      WHERE t2.EventType = 43 
          AND t1.Computer_ID = t2.Computer_ID
          AND t1.User_ID = t2.User_ID
      ORDER BY Event_time DESC
  ) t2
  WHERE t1.EventType = 42 
  and t1.Event_time BETWEEN '2017/10/10' and '2017/10/11'

sqlfiddle:http://sqlfiddle.com/#!18/4703f/2

Edit

This query is calculation cost totle time by every computer.

If that didn't your expect,Could you provide your expect result from my sqlfiddle sample data?

SELECT t1.Computer_ID,
       CONVERT(char(10), t1.Event_time,126) 'Dates',
       Convert(VARCHAR,DATEADD(ms,SUM(DATEDIFF(ms, '00:00:00.000', castTime)),'00:00:00.000'),108) 'totleCastTime'
FROM T1 t1 CROSS APPLY (
      SELECT TOP 1 *,
             DATEADD(s, DATEDIFF(s,t1.Event_time,t2.Event_time), 0) 'castTime'
      FROM T1 t2
      WHERE t2.EventType = 43 
          AND t1.Computer_ID = t2.Computer_ID
      ORDER BY Event_time DESC
  ) t2
  WHERE t1.EventType = 42 and t1.Event_time BETWEEN '2017/10/10' and '2017/10/11'
GROUP BY  t1.Computer_ID,
          CONVERT(char(10),t1.Event_time,126)

sqlfiddle:http://sqlfiddle.com/#!18/4703f/23

Upvotes: 1

Related Questions