Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2003
    Posts
    97

    Unanswered: Calculating time between each record

    Can someone pls take a look at this?
    Code:
    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
    
    -- not accurate b/c it returns 0 for all TimePerPageSec
    SELECT DATEDIFF(second,min(createDt),max(createDt)) AS TimePerPageSec
    FROM DigiTracker
    GROUP BY contact_id, ZinePageNumber
    need help figuring out the time spent (in seconds) between each record
    Last edited by gvee; 12-05-13 at 05:30. Reason: Put code in question

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    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
    Hope this helps.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Use LAG()

    I will guess at what the DDL should be. Table have keys, never use IDENTITY,and we have good temporal data types today. Oh, we ue CURRENT_TIMESTAMP now and not the old 1970's Sybase/ UNIX getdate()


    CREATE TABLE Tracker
    (track_id INTEGER NOT NULL,
    contact_id INTEGER NOT NULL,
    PRIMARY KEY (track_id, contact_id),
    creation_timestamp DATETIME2(0) NOT NULL,
    zine_page_number SMALLINT NOT NULL);

    SELECT track_id, contact_id, zine_page_number,
    creation_timestamp,
    DATEDIFF(second, creation_timestamp,
    LAG(creation_timestamp)
    OVER (PARTITION BY track_id
    ORDER BY contact_id))
    AS delta
    FROM Tracker;

    Untested.

  4. #4
    Join Date
    Mar 2003
    Posts
    97
    @imex - Still need to run a few more tests, but your code seems to work. Thanks!!

    @Celko - I'll try to experiment with your version.

    Thanks all!

  5. #5
    Join Date
    Mar 2003
    Posts
    97
    @imex,

    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. Any chance you could shed some light?

    Thanks again

  6. #6
    Join Date
    Mar 2003
    Posts
    97
    imex - any thoughts on how to get the code to show the difference between the first and second record reflect on the *first* TimePerPageSec record instead of the 2nd one?

    Thanks

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    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
    
    SELECT DigitrackID, Contact_id, CreateDt, ZinePageNumber
    ,  DateDiff(second, CreateDt, Lead(CreateDt)
          OVER (PARTITION BY contact_id ORDER BY CreateDt))
       FROM DigiTracker
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Apr 2012
    Posts
    213
    naceBal, if you are using SQL Server 2008 or earlier try:

    Code:
    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,
        c.createDt,
        n.createDt as NextCreateDt,
        c.ZinePageNumber,
        DATEDIFF(SECOND, c.createDt, n.createDt) as TimePerPageSec
    from CTE_RN as c
    left join CTE_RN as n
        on n.contact_id = c.contact_id and
           n.RN = c.RN + 1
    Hope this helps.

  9. #9
    Join Date
    Mar 2003
    Posts
    97
    Works great. Any way to show the top 3 TimePerPageSec DESC grouped by contact_id ?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Not with the sample data that you've provided, but it can be done pretty easily.

    For that problem, iMex's solution provides an easier answer. Just limit the RN values using a WHERE clause.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •