June 9, 2003 at 11:27 am
I have a table in my SQL 2000 DB that logs visitors to my website. Here are some of the values that I am inserting into my table. SessionID(unique session From IIS), Insert Date(Data type Datetime). What I want to do is report how many unique sessions were logged into the db for each day of any given month starting with the first day of the month and then ending with the last day or last reorded day depending on when the report is run.
For now I will just focus on the current month. How do I revise the example below in order to accomplish this using a cursor?
DECLARE @SessionID varchar
DECLARE @DatePlusOne numeric
DECLARE csrLoop CURSOR FOR
SELECT Count(DISTINCT(SessionID)) FROM dbo.WEBTracking
WHERE MONTH(InsDate) = MONTH(GetDate()) AND
YEAR(InsDate) = YEAR(GETDATE())
OPEN csrLoop
FETCH NEXT FROM csrLoop INTO @SessionID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT * FROM dbo.WEBTracking
WHERE SessionID = @SessionID
FETCH NEXT FROM csrLoop INTO @SessionID
END
CLOSE csrLoop
DEALLOCATE csrLoop
June 9, 2003 at 1:20 pm
Hi kbrady,
I don't think you need to use a cursor here -that is if the requirement is only to find the number of sessions for each month...
I think something like
select Count(distinct(SessionID)),datename(mm,InsertDate)
from dbo.WebTracking
group by datename(mm,InsertDate)
will give you a month wise count of the number of sessions logged in....
and for a daily counter you could use
select Count(distinct(SessionID)),Convert(Varchar(20),InsertDate,106)
from dbo.WebTracking
group by Convert(Varchar(20),InsertDate,106)
are these what you had in mind??
June 9, 2003 at 1:22 pm
You may consider using a view to the calculation for you, as shown below. All you have to do is just filter the view for the specific data of interest.
select
iyear = year(YourDate),
imonth = month (YourDate),
iday = day (YourDate),
icount = count(SessionID)
from dbo.WEBTracking
group by year(YourDate), month (YourDate), day (YourDate)
June 9, 2003 at 3:40 pm
I used this simple script to generate working data
Create Table #Temp_Sessions (Session_Id INT Identity(1,1),
EntryIntoSys DATETIME)
Declare @Counter INT
Set @Counter = 1
WHILE(@counter <= 7)
Begin
Insert #Temp_Sessions
Values (DateAdd(Day, 0, GetDate())) /** Increment 0 by 1 for more data **/
Set @Counter = @Counter + 1
End
Here the query to report unique session Ids
Select Count(Session_Id), Convert(varchar(30), EntryIntoSys, 1)
From #Temp_Sessions
Where DatePart(Month, EntryIntoSys) = '06' -- 'Change number for any month'
Group By Convert(varchar(30), EntryIntoSys, 1)
You need the datetime conversion b/c hour, minutes and seconds are not factors just the day. Much useful if this is used as a stored proc. b/c you can make month a required param.
MW
Edited by - mworku on 06/09/2003 3:42:30 PM
Edited by - mworku on 06/09/2003 3:43:14 PM
MW
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply