Post reply

Select rows based on 5min interval

  • 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.

  • 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

  • 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

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

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