Results 1 to 2 of 2

Thread: Help!!!!

  1. #1
    Join Date
    Dec 1969

    Default Help!!!!

    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. <BR><BR>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? <BR><BR><BR>DECLARE @SessionID varchar <BR>DECLARE @DatePlusOne numeric <BR>DECLARE csrLoop CURSOR FOR <BR><BR> <BR><BR> SELECT Count(DISTINCT(SessionID)) FROM dbo.WEBTracking <BR> WHERE MONTH(InsDate) = MONTH(GetDate()) AND <BR> YEAR(InsDate) = YEAR(GETDATE()) <BR><BR><BR>OPEN csrLoop <BR><BR>FETCH NEXT FROM csrLoop INTO @SessionID <BR><BR>WHILE @@FETCH_STATUS = 0 <BR>BEGIN <BR><BR>SELECT * FROM dbo.WEBTracking <BR>WHERE SessionID = @SessionID <BR><BR>FETCH NEXT FROM csrLoop INTO @SessionID <BR><BR>END <BR><BR>CLOSE csrLoop <BR>DEALLOCATE csrLoop

  2. #2
    Join Date
    Dec 1969

    Default Why do you need a loop?

    SELECT Count(DISTINCT(SessionID)), DAY(InsDate) <BR>FROM dbo.WEBTracking <BR>WHERE MONTH(InsDate) = MONTH(GetDate()) <BR>AND YEAR(InsDate) = YEAR(GETDATE()) <BR>GROUP BY Day(InsDate)<BR>ORDER BY Day(InsDate)<BR><BR>Presto. I can&#039;t see what else you need.<BR><BR>If you needed this for multiple months and/or years:<BR><BR>SELECT Count(DISTINCT(SessionID)), Year(InsDate), Month(InsDate), Day(InsDate)<BR>FROM dbo.WEBTracking <BR>WHERE InsDate &#062;= &#039;2002-1-1&#039; AND InsDate &#060; &#039;2004-1-1&#039;<BR>GROUP BY Year(InsDate), Month(InsDate), Day(InsDate)<BR>ORDER BY Year(InsDate), Month(InsDate), Day(InsDate)<BR><BR>The date range there is of course completely arbitrary. Use any range you wish. Notice that I carefully did *NOT* use BETWEEN. Because of the time component on InsDate, I really *DO* want to use &#062;= the start date but then &#060; one day after the end date. Okay?<BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts