sql - How to best store and aggregate daily, weekly, monthly visits for quick retrieval? -

i using sql server 2008 , coldfusion 9.

i need log visit web site. users logged in. need able retrieve how many times have logged in week, this, year , how many consecutive days, how stackexchange it. want able show calendar month , display days visitor visited.

i not sure of best way store data or retrieve it. initial thought create daily or weekly table records every hit every user. store userid , timestamp this.

table_visits_last_seven_days userid     vistitdatetime 101        2012-10-06 01:23:00 101        2012-10-06 01:24:00 101        2012-10-07 01:25:00 102        2012-10-07 01:23:00 102        2012-10-07 01:24:00 102        2012-10-07 01:25:00 

at end of each day, determine visited site , aggregate visits remove duplicate info. so, delete above data , insert table store data:

table_visits_all_time userid     vistitdate 101        2012-10-06 101        2012-10-07 102        2012-10-07 

this data easy query , wouldn't store unnecessary data. i'd have of data need determine how user visits site not effort.

is plan? there easier or better way? plan have gaping hole in it? ideas appreciated.

you change visitdatetime column declaration in table_visits_last_seven_days visitdate date, , log each visit in manner this:

insert table_visits_last_seven_days  select @userid, @visitdate not exists (   select 1 table_visits_last_seven_days (nolock)   userid=@userid , visitdate=@visitdate ) 

(@visitdate date type variable)