Calculate the total time spent in a week

user_12012109 0
Viewed 116

I want to calculate the total time spent in a week (the start of the week will be from a given date).

Here the given date is 2020-06-23 15:30:00. And the next week will start from 7 days after.

The duration of the activity will be calculated by the time gap between two rows for the same id if only the second is less than an hour after the first.

select t.UserName,
1 + datediff(second, '2020-06-23 15:30:00', CompletedOn) / (24 * 60 * 60 * 7) as week_num,
sum(datediff(minute, CompletedOn, next_ts)) as duration_minutes
from (select t.*,
lead(CompletedOn) over (partition by UserName order by CompletedOn) as next_ts
from #Results t
where t.CompletedOn >= '2020-06-23 15:30:00'
) t
where datediff(minute, CompletedOn, next_ts) < 60 and CompletedOn >='2020-06-23 15:30:00' and t.UserName = 'John B'
group by t.UserName, datediff(second, '2020-06-23 15:30:00', CompletedOn) / (24 * 60 * 60 * 7)
order by t.UserName, week_num;

The above query doesn't consider displaying the week_num if there is no entry for the date in the week, so it display the result as:

   UserName       | week_num |  duration_minutes
   ---------------|----------|------------------
   John B         |    1     |      38
   John B         |    2     |      10
   John B         |    3     |      0
   John B         |    5     |      0

However, I wanted the output as all the week number that falls up to the last date in the record.

   UserName       | week_num |  duration_minutes
   ---------------|----------|------------------
   John B         |    1     |      38
   John B         |    2     |      10
   John B         |    3     |      0
   John B         |    4     |      0
   John B         |    5     |      0

Some of the sample data:

       IF OBJECT_ID('tempdb..#Results') IS NOT NULL
        Truncate TABLE #Results
    else
        CREATE TABLE #Results
        (
            UserName varchar(20) not null,
            CompletedOn datetime not null
        )      
  
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-06-23T15:30:00'
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-06-23T15:31:00'
   --1 min
   
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-06-30T12:57:00'      
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-06-30T13:06:00'
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-06-30T13:34:00'
   --37 min 
   
   
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-06-30 15:31:00'      
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-06-30 15:33:00'
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-06-30 15:41:00'
   
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-06 08:41:00'      
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-07 14:29:00'
   
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-09 15:22:00'      
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-09 16:23:00'
   
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-21 15:34:00'      
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-21 17:00:00'
   
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-09 15:22:00'      
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-09 16:23:00'
   
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-21 15:34:00'      
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-21 17:00:00'
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-21 17:00:00'
   
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-23 06:34:00'      
 
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-23 08:28:00'
   INSERT INTO #Results (UserName, CompletedOn) 
   SELECT 'John B', '2020-07-23 08:28:00'

Db Fiddle

datediff sql-server sql
1 Answers

Consider joining to a recursive CTE that generates pair matches of UserName and all subsequent week_num to a defined end point. Below uses 10 but can extend to say 52.

WITH pairs AS (
    SELECT DISTINCT UserName, 1 AS week_num
    FROM #Results
    UNION ALL
    SELECT UserName, week_num + 1
    FROM pairs 
    WHERE week_num < 10                 -- ADJUST ## AS NEEDED
),  sub AS (
    SELECT t.UserName
         , t.CompletedOn
         , LEAD(CompletedOn) OVER (PARTITION BY t.UserName ORDER BY t.CompletedOn) as next_ts
    FROM #Results t
    WHERE t.CompletedOn >= '2020-06-23 15:30:00'
), main AS (
    SELECT sub.UserName
         , 1 + DATEDIFF(SECOND, '2020-06-23 15:30:00', sub.CompletedOn) / (24 * 60 * 60 * 7) AS week_num
         , SUM(DATEDIFF(MINUTE, sub.CompletedOn, sub.next_ts)) AS duration_minutes
    FROM sub
    WHERE DATEDIFF(MINUTE, sub.CompletedOn, sub.next_ts) < 60 
      AND sub.CompletedOn >='2020-06-23 15:30:00' 
      AND sub.UserName = 'John B'
    GROUP BY sub.UserName
           , DATEDIFF(SECOND, '2020-06-23 15:30:00', sub.CompletedOn) / (24 * 60 * 60 * 7)
)

SELECT pairs.UserName
     , pairs.week_num
     , ISNULL(main.duration_minutes, 0) AS duration_minutes
FROM pairs
LEFT JOIN main
   ON pairs.UserName = main.UserName
   AND pairs.week_num = main.week_num

OPTION (MAXRECURSION 0);

Online Demo

|    | UserName | week_num | duration_minutes |
|----|----------|----------|------------------|
| 1  | John B   | 1        | 38               |
| 2  | John B   | 2        | 10               |
| 3  | John B   | 3        | 0                |
| 4  | John B   | 4        | 0                |
| 5  | John B   | 5        | 0                |
| 6  | John B   | 6        | 0                |
| 7  | John B   | 7        | 0                |
| 8  | John B   | 8        | 0                |
| 9  | John B   | 9        | 0                |
| 10 | John B   | 10       | 0                |
user_14224510
Related