Search code examples
t-sqldurationsequential

t-sql sequential duration

I have a table that tracks user interactions with a customer’s record. I want to track the duration of each “touch” by a user, that is, the amount of time spent by each user every time we are required to access the customer’s record. I’ve got the basics down by using ROW_NUMBER() OVER (PARTITION BY | ORDER BY). The issue that I can’t get my pea brain around is how to separate two distinct user touches when those touches are sequential, but separated by a significant amount of time. I don’t know that I’ve explained that clearly, but the examples below should clarify:

Here is an example of data that I can query successfully:

DATE        TIME        USER
11/17/2011  1:30:47     ZDBatch
11/17/2011  1:32:40     ZDBatch
12/13/2011  10:39:46    EMSZC27
12/13/2011  10:45:48    EMSZC27

The desired result set is

DURATION (MIN)  USER
1.883   ZDBatch
6.033   EMSZC27

and is achieved with the following query (note that I’ve left some columns out of the above example):

; WITH CTE1 AS
    (
    SELECT  ROW_NUMBER() OVER (PARTITION BY thr.[tdate], thr.[job], thr.[who], thr.[moddate]  ORDER BY thr.[moddate]) AS RowNo,
    thr.[tdate], thr.[job], thr.[moddate], thr.[modtime], thr.[seq], thr.[who], 
         thr.[description], thr.[histtype], thr.[attachment], thr.[data1], thr.[data2]
    FROM    Trip_History_Reporting thr
    WHERE   thr.[tdate] = '2011-10-24' --AND thr.[job] IN ('0653-A', '0128-A')
        AND LEFT(thr.[description], 8) <> 'Recalled'
        AND (LEFT(thr.[who],5) = 'EMSZC' OR thr.[who] = 'ZDBatch')
        --ORDER BY thr.[moddate], thr.[modtime]
    )

SELECT  tdate, job, MIN(RowNo), MAX(RowNo), MIN(modtime) AS [Start], MAX(modtime) AS [End], 
    CAST(CAST(DATEDIFF(SECOND, MIN(modtime), MAX(modtime)) AS DECIMAL(6,0))/60 AS DECIMAL(8,2)) AS [Duration (Min)], 
    who, moddate
FROM    CTE1
GROUP BY    tdate, job, who, moddate

Here is an example of data that I cannot query successfully (sequential distinct touches by the same user):

DATE           TIME     USER
11/1/2011       6:34:48 EMSZC34
11/1/2011       6:35:08 EMSZC34
11/1/2011       6:35:08 EMSZC34
11/1/2011       6:35:08 EMSZC34
11/1/2011       6:35:08 EMSZC34
11/1/2011       6:35:08 EMSZC34
11/15/2011      11:08:32    EMSZC34
11/15/2011      11:09:14    EMSZC34
11/15/2011      11:09:14    EMSZC34
11/15/2011      11:09:14    EMSZC34
11/15/2011      11:09:14    EMSZC34
11/15/2011      11:09:14    EMSZC34

The issue arises when a “significant” amount of time passes between the end of the first touch and the beginning of the second touch (for argument’s sake, if an hour passes between touches, those are distinct touches).

The desired result set from the above data is

DURATION (MIN)      USER
     0.333          EMSZC34
     0.7             EMSZC34

This seems so simple, but I cannot figure it out. Thanks in advance for any ideas, suggestions, outright mockery at my thickheadedness.

I apologize for the delay in responding. I realize that this group is providing free assistance and I do not intend to take advantage of that by not replying quickly. I will do my best to not let this happen again.

Pursuant to EricZ's request, here is the example with the desired output:

DATE           TIME     USER
11/1/2011     6:34:48   EMSZC34
11/1/2011     6:35:08   EMSZC34
11/1/2011     6:35:08   EMSZC34
11/1/2011   6:35:08 EMSZC34
11/1/2011   6:35:08 EMSZC34
11/1/2011   6:35:08 EMSZC34
11/15/2011    11:08:32  EMSZC34
11/15/2011    11:09:14  EMSZC34
11/15/2011    11:09:14  EMSZC34
11/15/2011    11:15:24  EMSZC34
11/15/2011    11:26:38  EMSZC34
11/15/2011    11:34:55  EMSZC34
11/15/2011    11:36:22  EMSZC34

Desired output:

DURATION (MIN)  USER
0.333   EMSZC34
27.833  EMSZC34

Thanks in advance for any assistance you can provide. I've been researching islands and gaps in an attempt to resolve this dilemma, but I still can't figure it out.

Solution

  • Please try this. SQL Fiddle

    ;WITH c1 AS (
SELECT  DISTINCT CAST(LEFT(CONVERT( VARCHAR(20),moddate,112),10)+ ' ' + modtime AS DATETIME)as moddate ,moduser
FROM    Trip_History_Reporting
)
, c2 AS (
SELECT moddate,moduser,ROW_NUMBER() OVER (PARTITION BY moduser ORDER BY moddate) as row_no
FROM c1
)
, c3 AS (
SELECT c.moddate as startdate, cc.moddate as enddate, DATEDIFF(SECOND,c.moddate,cc.moddate) as diff,c.moduser
FROM c2 c 
INNER JOIN c2   cc 
    ON c.moduser= cc.moduser
    AND c.row_no = cc.row_no -1 
)
SELECT  *,diff/60.00 as diff_in_min
FROM    c3
WHERE diff <= 60*60 -- an hour in second

    UPDATE: Base on your updated question, please use the following query

    WITH c1 AS (
SELECT  DISTINCT CAST(LEFT(CONVERT( VARCHAR(20),moddate,112),10)+ ' ' + modtime AS DATETIME)as moddate ,moduser
FROM    Trip_History_Reporting
)
, c2 AS (
SELECT moddate,moduser,(SELECT MAX(cc.moddate) FROM c1 cc WHERE cc.moddate <= DATEADD(hour,1,c.moddate) AND cc.moddate > c.moddate AND c.moduser = cc.moduser) AS endtime
FROM c1 c
)
, c3 AS (
SELECT  moduser, MIN(moddate) as [start],endtime AS [end]
FROM    c2
WHERE endtime IS NOT NULL
GROUP BY moduser,endtime
)
SELECT  *,DATEDIFF(SECOND,[start],[end])/60.0  as diff_in_min 
FROM  c3