Day Average - 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 2008 Forums

Transact-SQL (2008)

Day Average

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2012-10-04 : 13:33:57

Hello,

I am counting Users and Files from a database as follows:

select
(select COUNT(*) from dbo.Users) as UsersCount,
(select COUNT(*) from dbo.Files) as FilesCount

I need to get an Average for each one.

Basically:

UsersAverage = UsersCount / Number Days
FilesAverage = FilesCount / Number Days

Both files and users have a DateTime Created Field.

How can I do this?

Thank You,

Miguel

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-04 : 14:08:38

You can divide by the number of days - for example:

SELECT COUNT(*)/COUNT(DISTINCT createDate) FROM dbo.Users

If the createDate column has time also, you would need to do this to extract only the date portion of it:

SELECT COUNT(*)/COUNT(DISTINCT DATEADD(dd,DATEDIFF(dd,0,createDate),0)) FROM dbo.Users

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2012-10-04 : 18:51:38

quote:
Originally posted by sunitabeck

You can divide by the number of days - for example:
SELECT COUNT(*)/COUNT(DISTINCT createDate) FROM dbo.Users
If the createDate column has time also, you would need to do this to extract only the date portion of it:
SELECT COUNT(*)/COUNT(DISTINCT DATEADD(dd,DATEDIFF(dd,0,createDate),0)) FROM dbo.Users

The problem is that I have dates which do not exist in Users table.
To make it easier I created a Calendar table with a column of type Date which contains all dates.

How can I calculate the average of users considering all dates, using the Calendar table, for a period of @BeginDate to @EndDate.

Thank You,
Miguel

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources