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.
| Author |
Topic |
|
aiken
Aged Yak Warrior
525 Posts |
 Posted - 2002-05-12 : 16:01:26
|
I operate a site that uses snitz forums (the same package used on this site). I'd like to see how many posts have been made each week to get an idea of where traffic is going. "Posts", for my purposes, is the number of topics + the number of replies (snitz keeps them in different tables, which is a mistake in my opinion, but oh well).Here's how I can get the number of new topics by week:select datepart(year,datetime),datepart(wk,datetime),count(*) from snitz_topics group by datepart(year,datetime),datepart(wk,datetime) order by datepart(year,datetime),datepart(wk,datetime) asc ...and here's how I can get the number of new replies by week:select datepart(year,datetime),datepart(wk,datetime),count(*) from snitz_reply group by datepart(year,datetime),datepart(wk,datetime) order by datepart(year,datetime),datepart(wk,datetime) asc ..is there any way to combine those two without using a temporary table or table variable? And yes, if you're familiar with snitz, I did add that datetime column as part of the integration into the website. It's a simple smalldatetime that defaults to getdate().Thanks-b |
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-05-12 : 16:15:11
|
Well, I found a way to do it with a subquery:select datepart(year,datetime),datepart(wk,datetime),count(*) +(select count(*) from snitz_topics st where datepart(year,st.datetime)=datepart(year,snitz_reply.datetime) and datepart(wk,st.datetime)=datepart(wk,snitz_reply.datetime)) from snitz_reply group by datepart(year,datetime),datepart(wk,datetime) order by datepart(year,datetime),datepart(wk,datetime) asc ...but is there any more elegant way than making the SQL server do that subquery for each row?Thanks-b |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-12 : 16:49:31
|
| A. You can do a UNION subquery:SELECT A.Year, A.Week, Count(*) PostsFROM(SELECT datepart(year,datetime) Year, datepart(wk,datetime) WeekFROM snitz_topicsUNION ALLSELECT datepart(year,datetime) Year, datepart(wk,datetime) WeekFROM snitz_reply) AS AGROUP BY A.Year, A.WeekORDER BY A.Year, A.WeekB. If you're using the standard Snitz code, you don't need to add a secondary datetime column, both the FORUM_TOPIC and FORUM_REPLY tables have columns for posted date that you can easily CONVERT to a datetime value for the purposes of your query. The code for it is here:SELECT Year, Week, Count(*) PostsFROM(SELECT datepart(year, convert(datetime, T_DATE)) Year, datepart(wk, convert(datetime, T_DATE)) WeekFROM FORUM_TOPICSUNION ALLSELECT datepart(year, convert(datetime, R_DATE)) Year, datepart(wk, convert(datetime, R_DATE)) WeekFROM FORUM_REPLY) AS AGROUP BY A.Year, A.WeekORDER BY A.Year, A.WeekC. Datetime is a reserved word, and you should not name columns, tables, etc. after reserved words. You'll end up with bugs in your code that will be hard to track down later.D. The Snitz code is designed to work with several different database software packages. If you look at the design and features of the Snitz code, you'll find that they are as efficiently designed as possible, even without requiring cross-platform compatibility. Topics are moderated separately from Replies, so there is no practical way to store them in the same table and keep their features separate. It also permits a separate security and audit model (someone could moderate replies without being able to moderate topics, for example) which, as mentioned earlier, is much easier to do when they are stored as two separate tables.For those reasons, I don't agree that this is a mistake in design on their part, but that's just my opinion. You can always change the design of the database and modify forum code to combine the two tables, but it seems unwieldly to me to do that just for the sake of simplifying one query.Edited by - robvolk on 05/12/2002 16:50:31 |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2002-05-12 : 17:18:55
|
| That's what I call a speedy and detailed reply; thanks. The union approach is exactly what I was looking for.I'm sort of stuck with the "datetime" column name; the existing app that I inherited used it exclusively, all over the place, in both the databse and ASP application. I considered using a different name on new parts of the application, but since I had already learned how to create clean code using reserved words as column names, I figured I'd just keep the app uniform throughout rather than having to know the creation date of a table to know its datetime field's name.I need the datetime column in the first place for indexing purposes, and as I adapt the Sntiz code more and more for my particular application, I'm moving every date reference over to use that rather than the nvarchar field that Snitz started out using.I see your point regarding moderation and using seperate tables, but I think I disagree. I would just use a column for "post_type" and assign moderation based on that; that way, new post types (say, site news, FAQ's, etc) could be created without making a new table for each one, and you could retain different moderation abilities for different types of posts.The multiple tables also create one of the admittedly few inefficiencies in the Snitz code; if you look at the topic.asp file, there is entirely seperate (and almost entirely redundant) code for the query and rendering of the original post and the querying and rendering of subsequent replies. Want to change the way the poster's name is displayed (maybe make it a link)? It's in two places. And each topic page requires an additional DB round trip.Just my opinion, though, and as Snitz is free and works so well, I can't complain too much. It is really very well written and continues to improve. I've just got a couple of quibbles about a few of its fundamentals (as you may have noticed).Cheers, and thanks-b |
|
|
|
|
|
|
|
|
Help with count(*) across multiple tables?