Post reply

Select rows based on 5 min interval

  • 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

    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 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 (

    SELECT PtID,

    EntryDate,

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

    FROM @Sample

    )

    SELECT PtID,

    MIN(EntryDate) AS EntryDate

    FROM cteLog

    GROUP BY PtID,

    Grp

    ORDER BY PtID,

    Grp

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

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

  • 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

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

  • 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

  • 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

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

    🙂

  • August 26, 2010 at 10:55 am

    #1212431

    Thank you so much for all great help!

    Have a great day!

    Minh V

  • August 26, 2010 at 8:14 pm

    #1212689

    Welcome, Min!

Viewing 10 posts - 1 through 9 (of 9 total)

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