Calculating time between each record

Last Post 12 Dec 2013 12:18 PM by Balance. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
Balance
New Member
New Member

--
11 Dec 2013 11:21 AM
Can someone please take a look at this?

CREATE TABLE [dbo].[DigiTracker](
[DigitrackID] [int] IDENTITY(1,1) NOT NULL,
[contact_id] [int] NOT NULL,
[createDt] [datetime] NOT NULL,
[ZinePageNumber] [smallint] NOT NULL
)

TRUNCATE TABLE [DigiTracker]

INSERT INTO [DigiTracker]
SELECT 1, GETDATE(), 1
UNION
SELECT 1, DATEADD(minute,4,GETDATE()), 2
UNION
SELECT 1, DATEADD(minute,1,GETDATE()), 7
UNION
SELECT 2, DATEADD(minute,11,GETDATE()), 1
UNION
SELECT 2, DATEADD(minute,14,GETDATE()), 9
UNION
SELECT 2, DATEADD(minute,6,GETDATE()), 13

WITH CTE_RN as
(
select
*,
ROW_NUMBER() OVER(PARTITION BY contact_id ORDER BY createDt) as RN
from DigiTracker
)

select
c.DigitrackID,
c.contact_id,
p.createDt as PrevCreateDt,
c.createDt,
c.ZinePageNumber,
DATEDIFF(SECOND, p.createDt, c.createDt) as TimePerPageSec
from CTE_RN as c
left join CTE_RN as p
on p.contact_id = c.contact_id and
p.RN = c.RN - 1

The first row for the "TimePerPageSec" field should always have the seconds between the 1st and 2nd createDt values, while the *last* "TimePerPageSec" value should be empty (because we don't know what the user did after that). I've tried playing with the code to no avail. Can someone shed some light?
Balance
New Member
New Member

--
12 Dec 2013 12:18 PM
Anybody out there??
You are not authorized to post a reply.

Acceptable Use Policy