in the cursor, it gets the data from a table that contains the IIS log (used logparser to transfer the logs to sql server). it will then calculate the hits and visits and insert this data into another table that will store a record of total hits and visits for each day.
the problem i am having is that there is no output from this current code. i can't seem to get it to loop back through the if statements and calculate.
any help?
Expand|Select|Wrap|Line Numbers
- set nocount on
- declare @datetime datetime, @username varchar(30), @holddate datetime, @holdusername varchar(30), @hits int, @visits int, @counter int, @inshits int, @insvisits int
- set @counter = 0
- declare visitcalculator cursor
- for select datetime, cs_username from testmetrics.dbo.alldata1 order by cs_username, datetime
- begin
- open visitcalculator
- fetch next from visitcalculator
- into @datetime, @username
- while @@fetch_status = 0
- begin
- if @username = @holdusername --compares username to previous row.
- begin
- set @hits = @hits + 1 --count hits
- set @holdusername = @username
- set @holddate = @datetime
- fetch next from visitcalculator into @datetime, @username
- end
- if @username = @holdusername and datediff(minute, @holddate, @datetime) > 30 --find a break of 30 or more minutes
- begin
- set @visits = @visits + 1 --count visits
- set @holdusername = @username
- set @holddate = @datetime
- fetch next from visitcalculator into @datetime, @username
- end
- if @username != @holdusername
- begin
- set @holdusername = @username
- set @holddate = @datetime
- fetch next from visitcalculator into @datetime, @username --grab next row.
- set @visits = @insvisits --transfer visits to insert
- set @hits = @inshits --transfer hits to insert
- set @visits = 0 --reset visits
- set @hits = 0 --reset hits
- set @counter = @counter + 1 --add to counter
- end
- if @counter > 1 --write data for each user into table
- begin
- insert into testmetrics.dbo.userinfo (username, date, hits, visits) values (@holdusername, @holddate, @inshits, @insvisits)
- end
- end
- end
- close visitcalculator
- deallocate visitcalculator