Post reply

Conditional Aggregation

  • May 28, 2012 at 11:28 pm

    #100476

    Lets say I have a table as

    MIDTime (Hh:Mm)

    M110:22

    M112:15

    M113:22

    M116:00

    M117:50

    I need to aggregate the counts for MID(M1) based on the condition "to aggregate values that are beyond 2 hrs window"

    So in above case , the valid ones are row1, row 3, row 4 .

    So total aggregated value for M1 =3

    I know I can write a cursor/loop to do this , but considering that I have a huge volume of data (~20M), is there a better way to solve this ?

    Thanks

  • May 29, 2012 at 12:01 am

    #1493968

    sridhar_kola (5/28/2012)

    Lets say I have a table as

    MIDTime (Hh:Mm)

    M110:22

    M112:15

    M113:22

    M116:00

    M117:50

    I need to aggregate the counts for MID(M1) based on the condition "to aggregate values that are beyond 2 hrs window"

    So in above case , the valid ones are row1, row 3, row 4 .

    So total aggregated value for M1 =3

    I know I can write a cursor/loop to do this , but considering that I have a huge volume of data (~20M), is there a better way to solve this ?

    The logic seems to be to always select the first row (10:22 here), skip the second row because 12:15 is within 2 hours of 10:22, select 13:22 because it is over 2 hours from 10:22, select 16:00 because it is over two hours from 13:22, and skip 17:50 because it is within two hours of 16:00. Is that right? Which version and edition of SQL Server are you using?

    Does you real table have more than one MID group? Is the time stored as a time data type, and is that the only way to order the records within a group?

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 29, 2012 at 12:09 am

    #1493970

    Yes your analysis is right .

    My SQL version is - SQL 2008 R2 - Enterprise

    My real table has several million MID and the time type is currently varchar but can be changed to suit our purpose.

  • May 29, 2012 at 12:29 am

    #1493975

    sridhar_kola (5/29/2012)

    Yes your analysis is right .

    My SQL version is - SQL 2008 R2 - Enterprise

    My real table has several million MID and the time type is currently varchar but can be changed to suit our purpose.

    You have posted your question in the SQL Server 7 and SQL Server 2000 area.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

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

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