calculate time difference on same column sql server

  • Hi

    I would like to get the time difference between rows per application and if the difference of time is less than 5 minutes then count as a single application.

    input:

    AppNumber  Employee Date Time_generatedat

    143270137         shiva 23/09/2019 12:24

    143270137       shiva 23/09/2019 12:25

    143270137        shiva 23/09/2019 12:27

    143270137      shiva 23/09/2019 13:16

    12339090 kalyan 23/09/2019 08:13

    12339090 kalyan 23/09/2019 09:14

    output:

    AppNumber Employee Date Time_generatedat output

    143270137 shiva 23/09/2019 12:24 1

    143270137 shiva 23/09/2019 12:25 1

    143270137 shiva 23/09/2019 12:27 1

    143270137 shiva 23/09/2019 13:16 2

    12339090 kalyan 23/09/2019 08:13 1

    12339090 kalyan 23/09/2019 09:14 2

  • You could use ROW_NUMBER() to partition by the AppNumber and order by the Time_GeneratedAt columns using SQL Server 2008 R2. Using the LAG() or LEAD() function would be more ideal on a newer version.

    Here is a quick way to calculate this count using the following code (I'm on us_english, so I had to adjust the dates a bit):

    IF OBJECT_ID(N'dbo.AppTimes', N'U') IS NOT NULL
    DROP TABLE dbo.AppTimes;

    IF OBJECT_ID(N'tempdb..#StageForAggregation', 'U') IS NOT NULL
    DROP TABLE #StageForAggregation;

    CREATE TABLE dbo.AppTimes
    (
    AppNumber bigint NOT NULL,
    Employee nvarchar (32) NOT NULL,
    Time_GeneratedAt datetime NOT NULL
    );

    INSERT INTO dbo.AppTimes (AppNumber, Employee, Time_GeneratedAt)
    VALUES (143270137, 'shiva', '09/23/2019 12:24'),
    (143270137, 'shiva', '09/23/2019 12:25'),
    (143270137, 'shiva', '09/23/2019 12:27'),
    (143270137, 'shiva', '09/23/2019 13:16'),
    (12339090, 'kalyan', '09/23/2019 08:13'),
    (12339090, 'kalyan', '09/23/2019 09:14');

    WITH CTE
    AS
    (
    SELECT AppNumber,
    Employee,
    Time_GeneratedAt,
    ROW_NUMBER() OVER (PARTITION BY AppNumber ORDER BY Time_GeneratedAt) AS RowNumAsc
    FROM dbo.AppTimes
    ),
    CTE2
    AS
    (
    SELECT AppNumber,
    Time_GeneratedAt,
    ROW_NUMBER() OVER (PARTITION BY AppNumber ORDER BY Time_GeneratedAt) AS RowNumAsc
    FROM dbo.AppTimes
    )
    SELECT CTE.AppNumber,
    CTE.Employee,
    CTE.Time_GeneratedAt,
    COALESCE(DATEDIFF(MINUTE, CTE2.Time_GeneratedAt, CTE.Time_GeneratedAt), 0) AS TimeDiff
    INTO #StageForAggregation
    FROM CTE
    LEFT JOIN CTE2
    ON CTE.AppNumber = CTE2.AppNumber
    AND CTE.RowNumAsc = CTE2.RowNumAsc + 1;

    SELECT *
    FROM #StageForAggregation;

    SELECT AppNumber,
    COUNT(*) AS AppCount
    FROM #StageForAggregation
    WHERE TimeDiff = 0 OR TimeDiff > 5
    GROUP BY AppNumber;

     

  • /* test data */
    drop table if exists #test_apps;
    go
    create table #test_apps(
    AppNumberint not null,
    Employeenvarchar(64) not null,
    Time_generatedatdatetime2 not null);
    go

    insert #test_apps(AppNumber, Employee, Time_generatedat) values
    (143270137,'shiva', '2019-09-23 12:24'),
    (143270137,'shiva', '2019-09-23 12:25'),
    (143270137,'shiva', '2019-09-23 12:27'),
    (143270137,'shiva', '2019-09-23 13:16'),
    (12339090 ,'kalyan', '2019-09-23 08:13'),
    (12339090 ,'kalyan', '2019-09-23 09:14');

    with dt_cte as(
    select
    *,
    lag(Time_generatedat,1) over (partition by AppNumber order by Time_generatedat) Time_lag
    from
    #test_apps t)
    select
    *,
    iif(datediff(mi, Time_lag, Time_generatedat)>=5,2,1) Variable
    from
    dt_cte
    order by 1, 3 asc;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • If you are using SQL 2008 you can't use LEAD and LAG. I think you might be able to use the windowed SUM function though.

    ;WITH Data AS
    (
    SELECT *
    FROM (VALUES (143270137, 'shiva', CONVERT(datetime, '20190923 12:24')),
    (143270137, 'shiva', '20190923 12:25'),
    (143270137, 'shiva', '20190923 12:27'),
    (143270137, 'shiva', '20190923 13:16'),
    (143270137, 'shiva', '20190923 14:16'),
    (143270137, 'shiva', '20190923 14:17'),
    (12339090 , 'kalyan', '20190923 08:13'),
    (12339090 , 'kalyan', '20190923 09:14'))T(AppNumber, Employee, DateTime_generatedat)
    ),
    CTE AS
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY AppNumber ORDER BY DateTime_generatedat ASC) RowNum, *
    FROM Data
    ),
    CTE2 AS
    (
    SELECT a.*,CASE
    WHEN ISNULL(DATEDIFF(mi,b.DateTime_generatedat,a.DateTime_generatedat),0) < 5 THEN 0
    ELSE 1
    END gt5
    FROM CTE a
    LEFT JOIN CTE b
    ON b.AppNumber = a.AppNumber
    AND b.RowNum+1 = a.RowNum

    )
    SELECT x.AppNumber,x.Employee,x.DateTime_generatedat,
    SUM(x.gt5) OVER (PARTITION BY x.AppNumber ORDER BY x.DateTime_generatedat)+1 AppN
    FROM CTE2 x
    ORDER BY AppNumber DESC,DateTime_generatedat
  • I hope below query will work for you.

    -- TABLE SCHEMA WITH SAMPLE DATA 
    DECLARE @Employee TABLE
    (
    AppNumber VARCHAR(20)
    , Employee VARCHAR(50)
    , [Date] DATE
    , Time_generatedat TIME
    )

    INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (143270137, 'shiva', '2019-09-23', '12:24:00:000')
    INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (143270137, 'shiva', '2019-09-23', '12:25:00:000')
    INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (143270137, 'shiva', '2019-09-23', '12:27:00:000')
    INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (143270137, 'shiva', '2019-09-23', '13:16:00:000')
    INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (12339090, 'kalyan', '2019-09-23', '08:13:00:000')
    INSERT INTO @Employee (AppNumber, Employee, [Date], [Time_generatedat]) VALUES (12339090, 'kalyan', '2019-09-23', '09:14:00:000')

    -- Microsoft SQL Server 2008R2 & Onwards
    ; WITH cte_stag_1
    AS
    (
    SELECT AppNumber
    , Employee
    , [Date]
    , Time_generatedat
    , ROW_NUMBER() OVER (ORDER BY AppNumber, Employee, [Date], Time_generatedat ASC) AS RowID
    FROM @Employee
    )
    , cte_stag_2
    AS
    (
    SELECT A.AppNumber
    , A.Employee
    , A.[Date]
    , A.Time_generatedat
    ,
    CASE
    WHEN DATEDIFF(MINUTE, A.Time_generatedat, ISNULL(B.Time_generatedat, A.Time_generatedat)) < 5
    THEN 5
    ELSE DATEDIFF(MINUTE, A.Time_generatedat, ISNULL(B.Time_generatedat, A.Time_generatedat))
    END AS Diff_In_Minutes
    FROM cte_stag_1 A
    LEFT JOIN cte_stag_1 B
    ON B.AppNumber = A.AppNumber
    AND B.Employee = A.Employee
    AND B.[Date] = A.[Date]
    AND B.RowID = A.RowID + 1
    )

    SELECT AppNumber
    , Employee
    , [Date]
    , Time_generatedat
    , DENSE_RANK() OVER (PARTITION BY AppNumber, Employee, [Date] ORDER BY Diff_In_Minutes ASC) AS [Output]
    FROM cte_stag_2

    -- Microsoft SQL Server 2012 & Onwards
    ; WITH cte_stag_1
    AS
    (
    SELECT AppNumber
    , Employee
    , [Date]
    , Time_generatedat
    ,
    CASE
    WHEN DATEDIFF(MINUTE, Time_generatedat, ISNULL(LEAD(Time_generatedat) OVER(PARTITION BY AppNumber, Employee, [Date] ORDER BY Time_generatedat ASC), Time_generatedat)) < 5
    THEN 5
    ELSE DATEDIFF(MINUTE, Time_generatedat, ISNULL(LEAD(Time_generatedat) OVER(PARTITION BY AppNumber, Employee, [Date] ORDER BY Time_generatedat ASC), Time_generatedat))
    END AS Diff_In_Minutes
    FROM @Employee
    )

    SELECT AppNumber
    , Employee
    , [Date]
    , Time_generatedat
    , DENSE_RANK() OVER (PARTITION BY AppNumber, Employee, [Date] ORDER BY Diff_In_Minutes ASC) AS [Output]
    FROM cte_stag_1
  • Window functions make queries like this easy.  Assuming you can't upgrade your SQL Server past 2008, can you offload your query to a newer version of SQL Server which supports window functions?  If this is a reporting query, you should be able to keep a current copy of your data on the newer server.

  • Jonathan's is the most correct. Mine only mimics your demo output and it's 2012+.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • I don't think going back just 1 row will be accurate, at least as I understand the requirements (which, of course, could be incorrect).  For example, assume times of:

    12:24; 12:25; 12:27; 12:30

    I would think 12:30 should start a new group, being 6 minutes past 12:24, but it is still within 3 minutes of 12:27.  Is that correct or not?  Not sure, but it's a legitimate possibility.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 8 posts - 1 through 7 (of 7 total)

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