Select rows based on 5min interval

• joemai

  Hall of Fame

  Points: 3496

  More actions

  August 25, 2010 at 5:31 pm

  #228361

  This is a re-post. I wrongly posted it in the 2k8, but I need help with 2k.

  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

  18271999 2010-08-03 07:39:00.000

  18271999 2010-08-03 07:44:00.000

  18271999 2010-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.

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  August 26, 2010 at 8:21 pm

  #1212691

  Can you explain this sequence?

  18271999 2010-08-03 07:49:00.000

  18271988 2010-08-03 07:51:00.000

  And what should happen after 08:05:00.000?

  Should the next record be after 08:10:00.000?

  Or should it be the first record of the next 5 min interval (from 08:09:00 to 08:13:59)?

  _____________
  Code for TallyGenerator

• WayneS

  SSC Guru

  Points: 95460

  More actions

  August 26, 2010 at 8:30 pm

  #1212693

  Sergiy (8/26/2010)

  ---

  Can you explain this sequence?

  18271999 2010-08-03 07:49:00.000

  18271988 2010-08-03 07:51:00.000

  And what should happen after 08:05:00.000?

  Should the next record be after 08:10:00.000?

  Or should it be the first record of the next 5 min interval (from 08:09:00 to 08:13:59)?

  1. Different ptit values.

  2. Grouping goes into 5 minute buckets. 7:49 falls in the 7:45:00.000 to 7:49:59.997 bucket, while 7:51 falls into the 7:50:00.000 to 7:54.59.997 bucket.

  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