calculate time difference on same column sql server

• sindhupavani123

  Right there with Babe

  Points: 754

  More actions

  September 27, 2019 at 12:26 pm

  #3683964

  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

  Attachments:

  You must be logged in to view attached files.

• Brahmanand Shukla

  SSC Eights!

  Points: 990

  More actions

  September 27, 2019 at 1:44 pm

  #3684026

  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

  SQL Server Carpenter - Consultant & Trainer
  Author of Learn T-SQL from Scratch