help writing this query (stats/projecting growth)

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 writing this query (stats/projecting growth)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-27 : 00:22:25

I'm trying to figure out the best way to track statistics on a logging table.

I have a table called "tblAccessLog" with the following columns

accessID(identity), mediaID, userID, dateAccessed

Each time a row is selected from the MediaTable, I insert a row into the log table, with the mediaID and the dateaccessed. The userID is the person viewing the media, which does not need to be monitored. (If the person has an account their userID is inserted, if they dont have an account "0" is inserted. It would be a nice bonus if I could differentiate between these two as well, but not necessary - especially if it makes the data that much harder to read and undersand)

I want to have the best possible display of which videos are being accessed how much, so I can project growth. Daily totals would be great, going back in time a set amount of days, or perhaps accepting start and end parameters.

What would be a good way to do this? I'm pretty flexible on how the data is returned and would love to hear some suggestions.

The only thing further that I need to do is

JOIN tblMedia Media on media.mediaID = tblAccessLog.mediaID

so that I can select a text description.

Thanks for any help on this, very much appreciated .. as always!

Mike123

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-27 : 04:09:03

Something like this??


SELECT A.dateAccessed, M.[Description],
		CASE WHEN A.userID = 0 THEN 'NONE' ELSE 'USER' END AS accountType,
		COUNT(A.mediaID) AS Accessed
FROM tblMedia M INNER JOIN tblAccessLog A
			ON M.mediaID = A.mediaID
GROUP BY A.dateAccessed, M.[Description], CASE WHEN A.userID = 0 THEN 'NONE' ELSE 'USER' END

Beauty is in the eyes of the beerholder

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2005-05-27 : 05:25:31

Hi Andy,

I think this is pretty on track, however the part that was bothering me most is the date part. This query brings back the same amount of rows as there are in the table because of the difference in seconds on the dateaccessed.

I sort of wanted them grouped by dateAccessed on the same day, or something to that effect so I can chart the growth. I could have a listing of the past 30 days or have a datestart dateEnd passed.

Thanks very much!
mike123

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-27 : 07:49:02

Try

SELECT CONVERT(varchar(10),A.dateAccessed,112),.......
FROM ......
GROUP BY CONVERT(varchar(10),A.dateAccessed,112)

Andy

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources