Select rows based on 5 min interval

• joemai

  Hall of Fame

  Points: 3496

  More actions

  August 25, 2010 at 3:22 pm

  #226930

  Hi All,

  I found this, but it is for Oracle. I'd like to apply the same concept in sql server. Can you please show me how to do it?

  http://forums.oracle.com/forums/thread.jspa?threadID=671100.

  Here is my sample data:

  create table #testtable (ptid int, entry_date datetime)

  go

  insert into #testtable

  select 18271999,'2010-08-03 07:39:00.000'

  union

  select 18271999,'2010-08-03 07:40:00.000'

  union

  select 18271999,'2010-08-03 07:41:00.000'

  union

  select 18271999,'2010-08-03 07:41:48.000'

  union

  select 18271999,'2010-08-03 07:42:00.000'

  union

  select 18271999,'2010-08-03 07:43:00.000'

  union

  select 18271999,'2010-08-03 07:44:00.000'

  union

  select 18271999,'2010-08-03 07:45:00.000'

  union

  select 18271999,'2010-08-03 07:46:00.000'

  union

  select 18271999,'2010-08-03 07:47:00.000'

  union

  select 18271999,'2010-08-03 07:48:00.000'

  union

  select 18271999,'2010-08-03 07:49:00.000'

  union

  select 18271999,'2010-08-03 07:50:00.000'

  union

  select 18271988,'2010-08-03 07:51:00.000'

  union

  select 18271988,'2010-08-03 07:53:00.000'

  union

  select 18271988,'2010-08-03 07:56:00.000'

  union

  select 18271988,'2010-08-03 08:05:00.000'

  I'd like to pull only the records from #testtable which is 5min apart.

  The result will look like this:

  PTID Entry_Date

  182719992010-08-03 07:39:00.000

  182719992010-08-03 07:44:00.000

  182719992010-08-03 07:49:00.000

  18271988 2010-08-03 07:51:00.000

  18271988 2010-08-03 07:56:00.000

  18271988 2010-08-03 08:05:00.000

  If there is no record for 5min interval, the system can pick the next higher records.

  Thanks for the helps.

  Minh V.

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  August 25, 2010 at 3:43 pm

  #1211969

  This is one way

  DECLARE@Sample TABLE

  (

  PtID INT NOT NULL,

  EntryDate DATETIME NOT NULL

  )

  INSERT@Sample

  (

  PtID,

  EntryDate

  )

  VALUES(18271999, '2010-08-03 07:39'),

  (18271999, '2010-08-03 07:40'),

  (18271999, '2010-08-03 07:41'),

  (18271999, '2010-08-03 07:41'),

  (18271999, '2010-08-03 07:42'),

  (18271999, '2010-08-03 07:43'),

  (18271999, '2010-08-03 07:44'),

  (18271999, '2010-08-03 07:45'),

  (18271999, '2010-08-03 07:46'),

  (18271999, '2010-08-03 07:47'),

  (18271999, '2010-08-03 07:48'),

  (18271999, '2010-08-03 07:49'),

  (18271999, '2010-08-03 07:50'),

  (18271988, '2010-08-03 07:51'),

  (18271988, '2010-08-03 07:53'),

  (18271988, '2010-08-03 07:56'),

  (18271988, '2010-08-03 08:05')

  ;WITH cteLog(PtID, EntryDate, Grp)

  AS (

  SELECTPtID,

  EntryDate,

  DATEDIFF(MINUTE, MIN(EntryDate) OVER (PARTITION BY PtID), EntryDate) / 5 AS Grp

  FROM@Sample

  )

  SELECTPtID,

  MIN(EntryDate) AS EntryDate

  FROMcteLog

  GROUP BYPtID,

  Grp

  ORDER BYPtID,

  Grp

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• joemai

  Hall of Fame

  Points: 3496

  More actions

  August 25, 2010 at 4:16 pm

  #1211979

  Thank you very much for your help. Yours worked for 2k5 perfectly. Unfortunately, we still had 2k. Is there a way to work around.

  Minh V.

• WayneS

  SSC Guru

  Points: 95460

  More actions

  August 25, 2010 at 4:30 pm

  #1211985

  joemai (8/25/2010)

  ---

  Thank you very much for your help. Yours worked for 2k5 perfectly. Unfortunately, we still had 2k. Is there a way to work around.

  Minh V.

  Did you notice that this is a SS2K8 (Sql Server 2008) forum? You get better, faster answers by posting in the correct forum. 😀

  So, adapting what Peso did:

  DECLARE @Sample TABLE

  (

  PtID INT NOT NULL,

  EntryDate DATETIME NOT NULL

  )

  INSERT @Sample

  (

  PtID,

  EntryDate

  )

  SELECT 18271999, '2010-08-03 07:39' UNION ALL

  SELECT 18271999, '2010-08-03 07:40' UNION ALL

  SELECT 18271999, '2010-08-03 07:41' UNION ALL

  SELECT 18271999, '2010-08-03 07:41' UNION ALL

  SELECT 18271999, '2010-08-03 07:42' UNION ALL

  SELECT 18271999, '2010-08-03 07:43' UNION ALL

  SELECT 18271999, '2010-08-03 07:44' UNION ALL

  SELECT 18271999, '2010-08-03 07:45' UNION ALL

  SELECT 18271999, '2010-08-03 07:46' UNION ALL

  SELECT 18271999, '2010-08-03 07:47' UNION ALL

  SELECT 18271999, '2010-08-03 07:48' UNION ALL

  SELECT 18271999, '2010-08-03 07:49' UNION ALL

  SELECT 18271999, '2010-08-03 07:50' UNION ALL

  SELECT 18271988, '2010-08-03 07:51' UNION ALL

  SELECT 18271988, '2010-08-03 07:53' UNION ALL

  SELECT 18271988, '2010-08-03 07:56' UNION ALL

  SELECT 18271988, '2010-08-03 08:05'

  SELECT PtID,

  MIN(EntryDate) AS EntryDate

  FROM (SELECT PtID, EntryDate,

  DATEDIFF(MINUTE, MIN(EntryDate) OVER (PARTITION BY PtID), EntryDate) / 5 AS Grp

  FROM @Sample ) cteLog

  GROUP BY PtID,

  Grp

  ORDER BY PtID,

  Grp

  Edit: Note that all I did was to replace the test data from a single values() (a SQL 2008 construct) to select... union all statements, and moved the CTE to a sub-query.

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  August 26, 2010 at 12:07 am

  #1212080

  WayneS (8/25/2010)

  ---

  joemai (8/25/2010)

  ---

  Thank you very much for your help. Yours worked for 2k5 perfectly. Unfortunately, we still had 2k. Is there a way to work around.

  Minh V.

  Edit: Note that all I did was to replace the test data from a single values() (a SQL 2008 construct) to select... union all statements, and moved the CTE to a sub-query.

  WayneS, OP is having SQL 2000.. so i guess OVER (PARTITION BY) wont work there..i dont have a handy SQL 2000 version for OVER (PARTITION BY)... it would be great if u could give OP and also me how to accomplish tat in SQL 2000...

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  August 26, 2010 at 12:16 am

  #1212083

  Ok, i used correlated sub-query to accomplish this task without OVER (PARTITION BY)

  Here is the SQL 2000 version of the code (Using the same concept from Peso)

  DECLARE @Sample TABLE

  (

  PtID INT NOT NULL,

  EntryDate DATETIME NOT NULL

  )

  INSERT @Sample

  (

  PtID,

  EntryDate

  )

  SELECT 18271999, '2010-08-03 07:39' UNION ALL

  SELECT 18271999, '2010-08-03 07:40' UNION ALL

  SELECT 18271999, '2010-08-03 07:41' UNION ALL

  SELECT 18271999, '2010-08-03 07:41' UNION ALL

  SELECT 18271999, '2010-08-03 07:42' UNION ALL

  SELECT 18271999, '2010-08-03 07:43' UNION ALL

  SELECT 18271999, '2010-08-03 07:44' UNION ALL

  SELECT 18271999, '2010-08-03 07:45' UNION ALL

  SELECT 18271999, '2010-08-03 07:46' UNION ALL

  SELECT 18271999, '2010-08-03 07:47' UNION ALL

  SELECT 18271999, '2010-08-03 07:48' UNION ALL

  SELECT 18271999, '2010-08-03 07:49' UNION ALL

  SELECT 18271999, '2010-08-03 07:50' UNION ALL

  SELECT 18271988, '2010-08-03 07:51' UNION ALL

  SELECT 18271988, '2010-08-03 07:53' UNION ALL

  SELECT 18271988, '2010-08-03 07:56' UNION ALL

  SELECT 18271988, '2010-08-03 08:05'

  SELECT PtID,

  MIN(EntryDate) AS EntryDate

  FROM (SELECT t2.PtID, t2.EntryDate,

  DATEDIFF(MINUTE, (select MIN(t1.EntryDate) from @Sample t1

  where t2.PtID = t1.PtID) , t2.EntryDate) / 5 AS Grp

  FROM @Sample t2 ) cteLog

  GROUP BY PtID,

  Grp

  ORDER BY PtID,

  EntryDate

• WayneS

  SSC Guru

  Points: 95460

  More actions

  August 26, 2010 at 5:14 am

  #1212215

  ColdCoffee (8/26/2010)

  ---

  WayneS, OP is having SQL 2000.. so i guess OVER (PARTITION BY) wont work there..i dont have a handy SQL 2000 version for OVER (PARTITION BY)... it would be great if u could give OP and also me how to accomplish tat in SQL 2000...

  Drat! I missed that ranking function. Thanks for catching it and producing a truly SQL 2000 version of this code!

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  August 26, 2010 at 6:08 am

  #1212231

  WayneS (8/26/2010)

  ---

  ColdCoffee (8/26/2010)

  ---

  WayneS, OP is having SQL 2000.. so i guess OVER (PARTITION BY) wont work there..i dont have a handy SQL 2000 version for OVER (PARTITION BY)... it would be great if u could give OP and also me how to accomplish tat in SQL 2000...

  Drat! I missed that ranking function. Thanks for catching it and producing a truly SQL 2000 version of this code!

  🙂

• joemai

  Hall of Fame

  Points: 3496

  More actions

  August 26, 2010 at 10:55 am

  #1212431

  Thank you so much for all great help!

  Have a great day!

  Minh V

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  August 26, 2010 at 8:14 pm

  #1212689

  Welcome, Min!