Average Daily Usage - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Average Daily Usage

Author

Topic

suekz
Starting Member

4 Posts

Posted - 2009-07-07 : 14:57:16

Hello,

My task is to caculate and display the average daily usage of a site within the past 90 days.

My tables are something like this:


ACCESSDATE   USERNAME
7/6/2009       John
7/6/2009       Joe
7/6/2009       Jim
7/5/2009       John
7/5/2009       Joe
7/5/2009       Jim

I also have another table that is used to identify the organization that the user belongs

USERNAME ORGANIZATION
John 1
Joe 1
Jim 2
Jeff 2
Jesse 3

I think my approach would be to combine the tables based on username.
Then, for each day, count the number of users and divide that by 90. My issue is, I'm not sure how to do a while loop/for loop, so I can get ths value. Or is there is a better way?

My attempt was something like this:



Dim dateNow As DateTime = DateTime.Today.Date
        Dim dateToday As Date = dateNow
        Dim date1Day As Date = dateNow.AddDays(-1)

For i=0 to 90
dim dailyAverage =  From rows In db.tPDM _
                      Join users In db.vUsers _
                      On rows.USERNAME Equals users.NAME _
                      Where rows.ACCESSDATE > date1Day.AddDays(-1) _
                      And rows.ACCESSDATE <= dateToday.AddDays(-1)_
                      And users.ORGANIZATION <> Nothing _
                      Select ORGANIZATION = users.ORGANIZATION,  users.NAME _
                      Distinct _
                      Group By ORGANIZATION _
                      Into UNIQUEUSERS = Count() _
                      Select New UserInfo _
                      With { .OrganizationName = ORGANIZATION, _
                             .UniqueHits = UNIQUEUSERS }

I have a couple more functions that is used to to update my dataset, but I know there has to be away to write a select statement that can accomplish this.

Thanks for any help!

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-07-07 : 15:56:32

Would something like this work?

SELECT AVG(HitCount*1.0) AS AverageHits
FROM
	(SELECT AccessDate, COUNT(UserName) AS HitCount 
	FROM YourTable
	GROUP BY AccessDate) a
WHERE AccessDate BETWEEN @StartDate AND @EndDate

Some days you're the dog, and some days you're the fire hydrant.

suekz
Starting Member

4 Posts

Posted - 2009-07-08 : 11:32:43

Thanks for the snippet. It's almost what I was looking for. But, the problem is, I'm trying to arrange the results by the organization that the user belongs to.

Site usage will then be divided by orgnization. Like..


Orgnization     average usage
org1                15
org2                685
org3                1231
org4                1250

Where the average usage is the count of users... please let me know if this doesn't make sense.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-08 : 12:14:03

[code]SELECT t2.Organisation,COUNT(*) AS Usage
FROM tbl2 t2
INNER JOIN tbl1 t1
ON t1.UserName=t2.Username
WHERE t1.ACCESSDATE > DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)-90
GROUP BY t2.Organisation
ORDER BY Usage ASC
[/code]

suekz
Starting Member

4 Posts

Posted - 2009-07-08 : 16:30:43

Thank you. How will I reference t1, if t1 is actually a SQL view?

Because, the query gave me Invalid object name 'dbo.vWindchillUsers'

suekz
Starting Member

4 Posts

Posted - 2009-07-08 : 16:32:48

Actually, that query doesn't pick up unique users? It diplays the list of organizations with usage, but the usage is not a daily average for the 90 day period, just the total usage in the 90 day period.

In VB and LINQ, I was able to get decent numbers:


 Dim dailyAverage As System.Linq.IQueryable(Of UserInfo) = Nothing
 Dim i As Integer
 
For i = 0 To 90
dailyAverage = From rows In db.tUsages _
               Join users In db.vUsers _
               On rows.USERNAME Equals users.NAME _
               Where rows.ACCESSDATE = date1Day.AddDays(-i) _
               And rows.ACCESSDATE <= dateToday.AddDays(-i) _
               And users.ORGANIZATION <> Nothing _
               Select ORGANIZATION = users.ORGANIZATION, users.NAME _
               Group By ORGANIZATION _
               UNIQUEUSERS = Count() _
               Select New UserInfo _
               With { _
               .OrganizationName = ORGANIZATION, _
               .UniqueHits = UNIQUEUSERS _
           }
        Next

The problem is, I'm not sure how its getting the correct values, since I haven't averaged the total.
I'm just looping through and saving to my custom type.

To make it more simple, is there any way I can keep count the occurence of a date within the 90 day period, divide that my 90 (to get the mean), and organize results by the user's organization.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-09 : 10:38:27

do you mean this?


SELECT t2.Organisation,COUNT(*)/90.0 AS Usage
FROM tbl2 t2
INNER JOIN tbl1 t1
ON  t1.UserName=t2.Username
WHERE t1.ACCESSDATE > DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)-90
GROUP BY t2.Organisation
ORDER BY Usage ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources