SQL Time Difference Totals

Question

+---------+-----------+----------+---------+
| FileID  | EventDate | UserName | EventID |
+---------+-----------+----------+---------+
| 1000001 | DateTime  | User1    |       1 |
| 1000001 | DateTime  | User1    |       3 |
| 1000001 | DateTime  | User1    |       3 |
| 1000001 | DateTime  | User1    |       1 |
| 1000001 | DateTime  | User1    |       3 |
| 1000001 | DateTime  | User1    |       3 |
| 1000001 | DateTime  | User1    |       3 |
| 1000002 | DateTime  | User1    |       1 |
| 1000002 | DateTime  | User1    |       3 |
+---------+-----------+----------+---------+

I have a table with event data organized similar to the above example. Each event has a FileID, EventID, UserName and a Datetime. Event ID 1 is the user opening the file and Event ID 3 is a save event. The final row with Event ID 3 indicates the file closing as each file is saved on closing. I need to be able to sum the total time a user was in a file (time at final event 3 - time at event 1) but am having trouble figuring out how to do this if a user has entered a file multiple times. Any ideas?

Answer 1

Using LEAD() and LAG() again, so this is SQL2012+.

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE t ( FileID int, EventDate datetime, UserName varchar(20), EventID int ) ;
INSERT INTO t (FileID, EventDate, UserName, EventID)
VALUES 
    (1000001,'2018-01-01T00:00:00.000','User1',1) /* OPEN 1 */
  , (1000001,'2018-01-01T00:01:15.000','User1',3) /* SAVE 1 */
  , (1000001,'2018-01-01T00:01:30.000','User1',3) /* CLOSE 1 */ /* 1 = 0:01:30 */
  , (1000001,'2018-01-01T00:02:00.000','User1',1) /* OPEN 2 */
  , (1000001,'2018-01-01T00:02:15.000','User1',3) /* SAVE 2 */
  , (1000001,'2018-01-01T00:02:30.000','User1',3) /* SAVE 2 */
  , (1000001,'2018-01-01T00:03:00.000','User1',3) /* SAVE 2 */
  , (1000002,'2018-02-01T00:00:00.500','User1',1) /* OPEN 1 */
  , (1000001,'2018-01-01T00:10:00.000','User1',3) /* CLOSE 2 */ /* 2 = 0:08:00 */
  , (1000002,'2018-02-01T00:01:00.000','User1',3) /* SAVE 1 */
  , (1000002,'2018-02-01T00:02:00.000','User1',3) /* CLOSE 1 */ /* 1 = 0:01:59.500 */
  , (1000088,'2018-02-10T00:00:00.000','NoCloseUser',1) /* OPEN - NOT CLOSED */
  , (1000099,'2018-02-10T00:00:00.000','NoOpenUser',3) /* CLOSED - NOT OPENED */
;

I added in a couple of bad records. One doesn't have a close date, but will still show up in the results. The is purely bad and didn't record an open date, but won't mess up the data. The Opened-But-Not-Closed record can be filtered in the query.

Query:

; WITH closes AS (
  SELECT * 
  FROM (
    SELECT t.FileID, t.UserName, t.EventDate, t.EventID
      , LEAD(t.EventID) OVER (PARTITION BY t.FileID, t.UserName ORDER BY t.EventDate) AS leadEvent 
    FROM t
  ) s1
  WHERE s1.EventID = 3 AND COALESCE(s1.leadEvent,1) = 1
)
, opens AS (
  SELECT t.FileID, t.UserName, t.EventDate
    , LEAD(t.EventDate) OVER ( PARTITION BY t.FileID, t.UserName ORDER BY t.EventDate ) AS nextOpen  
  FROM t 
  WHERE t.EventID = 1
)
SELECT s2.FileID, s2.UserName, SUM(minOpen) AS totalMinOpen 
FROM (
  SELECT o.FileID, o.UserName
    , o.EventDate as openDate
    , o.nextOpen
    , c.EventDate closeDate
    , c.leadEvent
    , DATEDIFF(second, o.EventDate,COALESCE(c.EventDate,getDATE()))/60.0 AS minOpen
  FROM opens o
  LEFT OUTER JOIN closes c ON o.FileID = c.FileID
    AND o.UserName = c.UserName
    AND o.EventDate <= COALESCE(c.EventDate,getDATE()) 
    AND ( c.leadEvent IS NOT NULL OR o.nextOpen IS NULL )
) s2
GROUP BY s2.FileID, s2.UserName

Results:

|  FileID |    UserName | totalMinOpen |
|---------|-------------|--------------|
| 1000001 |       User1 |          9.5 |
| 1000002 |       User1 |            2 |
| 1000088 | NoCloseUser |        28694 |

I use LEAD() and LAG() to determine the areas between where a file was closed and then opened again. I used CTEs to store the individual queries I used to determine the opens and closes. I diff'ed it out to seconds and then divided by 60.0 to get a decimal representation of the minutes. I'm sure this query can be optimized a bit.

Answer 2

Just make some fancy joins:

select fileID,userName,eventDate 'startDate',min(t2.eventDate) 'nextStart'
into #temp1
from logTable t1
left join logTable t2 on t1.fileID=t2.fileID and t1.userName=t2.userName and t2.eventID=1 and t1.eventDate<t2.eventDate
where t1.eventID=1
group by fileID,eventDate,userName

select fileID,userName,startDate,max(t2.eventDate) 'endDate'
from #temp1 t1
left join logTable t2 on t1.fileID=t2.fileID and t1.userName=t2.userName and t2.eventID=3 and t2.eventDate>t1.startDate and t2.eventDate<t1.nextStart 

You wan't be able to get the the time spent though until the user opens the same file another time since you don't have a closestatus. But you could modify the second join to ...(t2.eventDate<t1.nextStart or t1.nextStart is null) This will get the start time to the last savepoint

Answer 3

create table events (FileID int, EventDate datetime, UserName varchar(10), EventID int);
insert into events values
(1000001, '20180101 10:00:00', 'User1', 1 ),
(1000001, '20180101 10:30:00', 'User1', 3 ),
(1000001, '20180101 10:45:00', 'User1', 3 ),
(1000001, '20180102 11:00:00', 'User1', 1 ),
(1000001, '20180102 11:30:00', 'User1', 3 ),
(1000001, '20180102 11:45:00', 'User1', 3 ),
(1000001, '20180102 12:00:00', 'User1', 3 ),
(1000002, '20180101 05:00:00', 'User1', 1 ),
(1000002, '20180101 05:45:00', 'User1', 3 );

GO

9 rows affected

select FileID, sum(minutes) totalTime
from (
      select FileID, grp, datediff(minute, min(EventDate), max(EventDate)) minutes
      from (
            select FileID, EventDate, UserName, EventID,
                   sum(iif(EventID = 1,1,0)) over (order by FileID, EventDate) grp
            from   events
           ) t
      group by FileId, grp
     ) t2
group by FileID
GO

 FileID | totalTime
------: | --------:
1000001 |       105
1000002 |        45

dbfiddle here