help with query - 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)

help with query

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-01-16 : 04:28:11

Hi,

I'm trying to create a stats page for a forum, just like sqlteam. I'm not sure whether I should do this calculating on the asp.net page, or in the SPROC. I'd like to try the SPROC, but I'm not too handy at it and need some assistance.

I have a table as structured below.

I want to get posts per day as calculated by using getDate and the registrationDate to calculate the number of days the user has been a member. I then want to display "posts per day","postsPerWeek", "postsPerMonth","postsPerYear"

I would also to display a column that brings a value for reaching certain post marks. For example columns such as "daysToReach5kPosts", "daysToReach10kPosts"

Thanks very much for any assistance!
mike123



SELECT .... FROM tblUserDetails WHERE userID = 100


CREATE TABLE [dbo].[tblUserDetails](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[NameOnline] [varchar](15) NULL,
	[DateRegistered] [datetime] NULL,
	[Forum_Posts] [int] NULL 
)
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 04:41:06

I think you might need a calendar table as well to link with data in current table to get counts based on day,week,month,year....

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-01-16 : 05:29:05

Once you've got the calendar table linked to the User - UserUsage tables , itjust a matter of defining some sql statements based around COUNT (as mentioned above) for the various time periods

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-01-16 : 14:58:34

Hmm not sure what a calendar table is, I will have to look that up. I am wondering why I need it tho, I think that postsperweek is days x7 should be accurate enough ? also x30 for month etc....

any help is much appreciated!!

thanks!
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources