help figuring out 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 2000 Forums

Transact-SQL (2000)

help figuring out average

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-03-09 : 17:45:05

Hi,

I'm trying to select a column that returns the average logins per day. This is computed by (getDate() - joinDate) = amount of days

then loginCount / amount of days = login

select userID, loginCount, dateJoin FROM userDetails

if anyone can help me get this right I keep messing up the sql syntax and I believe I'm having problems with the datatypes as well...

thanks once again guys!

mike123

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-09 : 18:15:42

This should do it.


select 
	Logins_Per_Day =
		-- Compute to 2 decimal places
		convert(numeric(10,2),round(
		-- Multiply by 1.000000 to cast to numeric
		(loginCount*1.000000) /
		(datediff(dd,joinDate,getdate())*1.000000)
		,2))
from
	-- Test data
	( select loginCount = 167, joinDate ='20060101' ) a

Results:


Logins_Per_Day 
-------------- 
2.49

(1 row(s) affected)

CODO ERGO SUM

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2006-03-09 : 19:58:12

Hi Michael,

that worked perfectly, however I realized I need to extend some functionality and I'm not sure if its possible with this structure. ( I've tried to get it going but can't figure it out)

I need to be able to determine the loginCount for each row in userDetails. I need to be able to order descending by the generated column "LoginsPerDay"

I also need to be able to add "WHERE dateJoin > XX/XX/XXXX" into the query for the table userDetails.

Thanks very much if you can continue assistance or to anyone else

mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources