Post reply

Is Calculating time with a single logDateTime Column and unique ID's possible

  • December 11, 2017 at 12:57 pm

    #325015

    CREATE TABLE [dbo].[TransLog](
        [TransactionID] [int] IDENTITY(1,1) NOT NULL,
        [ID] [int] NULL CONSTRAINT [SomeContraint] DEFAULT ((0)),
        [Type] [nvarchar](5) NULL,
        [Name] [nvarchar](800) NULL,
        [ActionType] [nvarchar](800) NULL,
        [UserID] [nvarchar](15) NULL,
        [LogDateTime] [datetime] NULL,
    CONSTRAINT [Trans_PK] PRIMARY KEY NONCLUSTERED
    (
        [TransactionID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    Data on the table is in the format below. 

    111630    1345    SomeType    Page1    Display    User1    2013-01-23 13:11:15.000
    111631    1345    SomeType     Page2    Save    User1       2013-01-23 13:12:05.000
    111630    1345    SomeType    Page1    Display    User1    2013-01-23 16:11:15.000
    111631    1345    SomeType     Page2    Save    User1       2013-01-23 18:12:05.000

    Expected Results 
    User1 Spent X number of minutes on a page1 and User1 spent x number of minutes on ID 1345

    Is calculating time with a single column possible

  • December 11, 2017 at 2:04 pm

    #1971664

    What version of SQL Server are you using?  SQL 2012 and above have the functions LEAD & LAG which you could use:
    select
        *,
        DATEDIFF(MINUTE, LAG(LogDateTime, 1) OVER (PARTITION BY [ID], [TransactionID] ORDER BY [ID], [TransactionID], LogDateTime), LogDateTime) AS [MinuteCnt]
    from [dbo].[TransLog]

    From here you can GROUP and SUM however you like.

    FYI, the data you provided violates your primary key constraint.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply