May 28, 2012 at 11:28 pm
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
sridhar_kola (5/28/2012)
Lets say I have a table asMIDTime (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?
May 29, 2012 at 12:09 am
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
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply