Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rjdpa
Starting Member
2 Posts |
 Posted - 2009-12-03 : 11:25:16
|
| I have over 1 million record... One of the fields has a datetime stamp.I want to find out the maximum number of records within a one minute time frame. In other words, I want to find out the max number of records added within a minutes time.What would be the best way to do this? |
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-12-03 : 12:12:34
|
| Try like this.... select max(no_of_occ),date_time from (select row_number() over( PARTITION by convert(varchar,date_time_column,101)+ ' '+cast(datepart(hh,date_time_column) as varchar(5))+':'+cast(datepart(mi,date_time_column) as varchar(5)) order by convert(varchar,date_time_column,101)+ ' '+cast(datepart(hh,date_time_column) as varchar(5))+':'+cast(datepart(mi,date_time_column) as varchar(5))) as no_of_occ,convert(varchar,date_time_column,101)+ ' '+cast(datepart(hh,date_time_column) as varchar(5))+':'+cast(datepart(mi,date_time_column) as varchar(5)) as date_time from con_tbl_user_log) agroup by date_timeSenthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
 |
|
|
rjdpa
Starting Member
2 Posts |
Posted - 2009-12-03 : 12:36:58
|
| worked! thank you very much!!! |
|
|
|
senthil_nagore
Master Smack Fu Yak Hacker
1007 Posts |
Posted - 2009-12-03 : 12:51:55
|
quote:
Originally posted by rjdpa
worked! thank you very much!!!
Welcome  Senthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-12-04 : 02:47:55
|
orselect max(no_of_acc) as no_of_acc,date_time_col from(select dateadd(minute,datediff(minute,0,date_time_col),0) as date_time_col,row_number() over (partition by dateadd(minute,datediff(minute,0,date_time_col),0) order by dateadd(minute,datediff(minute,0,date_time_col),0)) as no_of_accfrom con_tbl_user_log) as tgroup by date_time_col MadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-12-04 : 02:54:27
|
quote:
Originally posted by senthil_nagore
Try like this.... select max(no_of_occ),date_time from (select row_number() over( PARTITION by convert(varchar,date_time_column,101)+ ' '+cast(datepart(hh,date_time_column) as varchar(5))+':'+cast(datepart(mi,date_time_column) as varchar(5)) order by convert(varchar,date_time_column,101)+ ' '+cast(datepart(hh,date_time_column) as varchar(5))+':'+cast(datepart(mi,date_time_column) as varchar(5))) as no_of_occ,convert(varchar,date_time_column,101)+ ' '+cast(datepart(hh,date_time_column) as varchar(5))+':'+cast(datepart(mi,date_time_column) as varchar(5)) as date_time from con_tbl_user_log) agroup by date_timeSenthil.C------------------------------------------------------[Microsoft][ODBC SQL Server Driver]Operation canceledhttp://senthilnagore.blogspot.com/
Work on DATETIME not VARCHAR  MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
HELP