Author |
Topic  |
|
cicciput
Starting Member
19 Posts |
Posted - 28 April 2004 : 14:13:56
|
Hi I need a SQL string to get the top x posters of the month but don't know how to get it...  I want to implement a monthly competition and don't need the top posters ever but only related to the last month...
Can someone help me??? 
Tnx a lot!
|
Never say D'oh! http://www.blanksheetmusic.net |
Edited by - ruirib on 12 June 2004 11:23:30 |
|
DavidRhodes
Senior Member
   
United Kingdom
1222 Posts |
Posted - 28 April 2004 : 16:11:56
|
There's a mod somewhere to show top posters but not in the last/current month. You could look at that and modify the code to just show post in the past x days |
The UK MkIVs Forum |
 |
|
ruirib
Snitz Forums Admin
    
Portugal
26364 Posts |
|
cicciput
Starting Member
19 Posts |
Posted - 29 April 2004 : 07:30:35
|
Acc! I searched for "top posters" in all forums but didn't have this result... don't know why... Tnx a lot ruirib! |
Never say D'oh! http://www.blanksheetmusic.net |
 |
|
ruirib
Snitz Forums Admin
    
Portugal
26364 Posts |
|
cicciput
Starting Member
19 Posts |
Posted - 10 June 2004 : 12:41:17
|
Thanks to rurib I found this SQL (I modified it only a bit to get member_id too etc...):
strSql = " SELECT TOP 15 R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, COUNT(R_AUTHOR) AS T_POSTS FROM "
strSql = strSql & "(SELECT R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, R_DATE FROM (" & strMemberTablePrefix &"MEMBERS INNER JOIN " & strTablePrefix & "REPLY ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "REPLY.R_AUTHOR) WHERE R_DATE >= '" & strStartDate & "' AND R_DATE < '" & strEndDate & "'" & vbCrLf
strSql = strSql & " UNION " & VbCrLf
strSql = strSql & "SELECT T_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, T_DATE FROM (" & strMemberTablePrefix & "MEMBERS INNER JOIN " & strTablePrefix & "TOPICS ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "TOPICS.T_AUTHOR) WHERE T_DATE >= '" & strStartDate & "' AND T_DATE < '" & strEndDate &"') As MyView "
strSql = strSql & "GROUP BY R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS ORDER BY Count(R_AUTHOR) DESC, M_POSTS DESC;"
resulting for today:
SELECT TOP 15 R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, COUNT(R_AUTHOR) AS T_POSTS FROM
(SELECT R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, R_DATE FROM
(FORUM_MEMBERS INNER JOIN FORUM_REPLY ON FORUM_MEMBERS.MEMBER_ID=FORUM_REPLY.R_AUTHOR) WHERE R_DATE >= '20040501000000' AND R_DATE < '20040610182552'
UNION
SELECT T_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, T_DATE FROM
(FORUM_MEMBERS INNER JOIN FORUM_TOPICS ON FORUM_MEMBERS.MEMBER_ID=FORUM_TOPICS.T_AUTHOR) WHERE T_DATE >= '20040501000000' AND T_DATE < '20040610182552') As MyView
GROUP BY R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS
ORDER BY Count(R_AUTHOR) DESC, M_POSTS DESC;
but making the sum of the resulting month posts they don't correspond with total posts.
There's one thing I don't understand: only R_AUTHOR is COUNTED in the first SELECT, where all the T_AUTHOR go after the UNION operation? How can this work right?
I'm not handy with SQL so who can help me? Pleaase  |
Never say D'oh! http://www.blanksheetmusic.net |
Edited by - cicciput on 10 June 2004 12:42:24 |
 |
|
ruirib
Snitz Forums Admin
    
Portugal
26364 Posts |
Posted - 10 June 2004 : 22:06:10
|
When you have a UNION query (like you have here), it's the first query that names the columns you get. So you have R_AUTHOR in the first query (first field from that query) and the first field from the 2nd query is T_AUTHOR. When the union is performed, you have the first column of the union query named R_AUTHOR, but this column will include all the values for the first column from both queries. Seems a bit confusing but it really isn't.
I can only see an explanation for the discrepance between values returned by this query and post counts. Post counts include topics and replies deleted since they were posted, while this query counts only existing posts. Thus, it is possible that the totals for each member from this query are less than the post count for each member. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
cicciput
Starting Member
19 Posts |
Posted - 11 June 2004 : 07:45:42
|
Ok, now I understand how it works!
The strange thing with this difference is that the sum of single months posts are higher than total posts!!! My forum is quite young so I can control data and I have no forums that don't increase total member's posts. Boh, maybe there's something wrong in my code, or, sometimes, when posting a new thread or an answer, in particular conditions, the total posts are not updated...
Thanks again rurib Bye |
Never say D'oh! http://www.blanksheetmusic.net |
 |
|
ruirib
Snitz Forums Admin
    
Portugal
26364 Posts |
|
cicciput
Starting Member
19 Posts |
Posted - 12 June 2004 : 05:54:05
|
Ok, I found the error, it was mine (of course ) cause I used WRONG start and end dates...

Thanks for the help! |
Never say D'oh! http://www.blanksheetmusic.net |
Edited by - cicciput on 12 June 2004 07:23:06 |
 |
|
ruirib
Snitz Forums Admin
    
Portugal
26364 Posts |
|
stwilson
Junior Member
 
USA
385 Posts |
Posted - 13 June 2004 : 01:28:20
|
cicciput,
Can you share what your final code looked like? Sounds like an interesting MOD. |
Shannon RidingArizona.com |
 |
|
cicciput
Starting Member
19 Posts |
Posted - 14 June 2004 : 15:02:55
|
Of course I can! (I'm honored! )
Here the code for the top_month_posters.asp page (I added a filter, now you can see all members or only mods or onlr normals): You can see an example here
<!--#INCLUDE FILE="config.asp" -->
<!--#INCLUDE FILE="inc_sha256.asp"-->
<!--#INCLUDE FILE="inc_header.asp" -->
<!--#INCLUDE FILE="inc_func_common.asp" -->
<%
if strDBNTUserName = "" then
Err_Msg = "<li>You must be logged in to view the Members List</li>"
Response.Write " <table width=""100%"" border=""0"">" & vbNewLine & _
" <tr>" & vbNewLine & _
" <td><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" & vbNewLine & _
" " & getCurrentIcon(strIconFolderOpen,"","") & " <a href=""default.asp"">All Forums</a><br />" & vbNewLine & _
" " & getCurrentIcon(strIconBar,"","") & getCurrentIcon(strIconFolderOpenTopic,"","") & " Member Information</font></td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" </table>" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strHeaderFontSize & """ color=""" & strHiLiteFontColor & """>There Was A Problem!</font></p>" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strHiLiteFontColor & """>You must be logged in to view this page</font></p>" & vbNewLine & _
" <p align=""center""><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """><a href=""JavaScript:history.go(-1)"">Back to Forum</a></font></p>" & vbNewLine & _
" <br />" & vbNewLine
WriteFooter
Response.End
end if
Response.Write " <script language=""JavaScript"" type=""text/javascript"">" & vbNewLine & _
" <!--" & vbNewLine & _
" function setFilterSubmit() { document.setFilter.submit() }" & vbNewLine & _
" // -->" & vbNewLine & _
" </script>" & vbNewLine
Dim reqFilter, filter
reqFilter = Request.QueryString("Filter")
select case reqFilter
case ""
filter = ""
case "all"
filter = ""
case "normal"
filter = " AND M_LEVEL = 1"
case "mods"
filter = " AND M_LEVEL = 2"
end select
Response.Write " <table width=""100%"" border=""0"">" & vbNewLine & _
" <tr>" & vbNewLine & _
" <form name=""setFilter"" action=""top_month_posters.asp"" method=""GET"">" & vbNewLine & _
" <td><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" & vbNewLine & _
" " & getCurrentIcon(strIconFolderOpen,"","") & " <a href=""default.asp"">All Forums</a><br />" & vbNewLine & _
" " & getCurrentIcon(strIconBar,"","") & getCurrentIcon(strIconFolderOpenTopic,"","") & " Top Month Posters</font>" & vbNewLine & _
" <select name=""filter"" onchange=""setFilterSubmit()"">" & vbNewLine & _
" <option value=""all"""
if (reqFilter = "all" or reqFilter = "") then Response.Write " SELECTED"
Response.Write ">All members</option>" & vbNewLine & _
" <option value=""normal"""
if reqFilter = "normal" then Response.Write " SELECTED"
Response.Write ">Normal Members only</option>" & vbNewLine & _
" <option value=""mods"""
if reqFilter = "mods" then Response.Write " SELECTED"
Response.Write ">Moderators only</option>" & vbNewLine & _
" </select>" & vbNewLine & _
" </td></form>" & vbNewLine & _
" </tr>" & vbNewLine & _
" <tr>" & vbNewLine & _
" <td>" & vbNewLine
Dim strConn, conn
thisMonth = Now()
strStartDate = doublenum(Year(thisMonth)) & doublenum(Month(thisMonth)) & "01000000"
nextMonth = dateadd("m",+1,thisMonth)
strEndDate = doublenum(Year(nextMonth)) & doublenum(Month(nextMonth)) & "01000000"
strSql = " SELECT TOP 15 R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, COUNT(R_AUTHOR) AS T_POSTS FROM "
strSql = strSql & "(SELECT R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, R_DATE FROM (" & strMemberTablePrefix &"MEMBERS INNER JOIN " & strTablePrefix & "REPLY ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "REPLY.R_AUTHOR) WHERE R_DATE >= '" & strStartDate & "' AND R_DATE < '" & strEndDate & "'" & filter & vbCrLf
strSql = strSql & " UNION " & VbCrLf
strSql = strSql & "SELECT T_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, T_DATE FROM (" & strMemberTablePrefix & "MEMBERS INNER JOIN " & strTablePrefix & "TOPICS ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "TOPICS.T_AUTHOR) WHERE T_DATE >= '" & strStartDate & "' AND T_DATE < '" & strEndDate &"'" & filter & ") As MyView "
strSql = strSql & "GROUP BY R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS ORDER BY Count(R_AUTHOR) DESC, M_POSTS DESC;"
' Response.Write strSql & "<BR>"
Response.Write " <table border=""0"" width=""300"" cellspacing=""0"" cellpadding=""0"" align=""center"">" & vbNewLine & _
" <tr>" & vbNewLine & _
" <td bgcolor=""" & strTableBorderColor & """>" & vbNewLine & _
" <table border=""0"" width=""100%"" cellspacing=""1"" cellpadding=""3"">" & vbNewLine & _
" <tr>" & vbNewLine
Response.Write " <td align=""center"" bgcolor=""" & strHeadCellColor & """><b><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strHeadFontColor & """>Rank</font></b></td>" & vbNewLine
Response.Write " <td align=""center"" bgcolor=""" & strHeadCellColor & """><b><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strHeadFontColor & """>Member Name</font></b></td>" & vbNewLine
Response.Write " <td align=""center"" bgcolor=""" & strHeadCellColor & """><b><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strHeadFontColor & """>Posts This Month</font></b></td>" & vbNewLine & _
" </tr>" & vbNewLine
intI = 0
rank = 1
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSql, strConnString
While Not Rs.EOF
'Response.Write rs("M_NAME") & " " & rs("T_POSTS") & " " & rs("MEMBER_ID") & "<br>"
if intI = 1 then
CColor = strAltForumCellColor
else
CColor = strForumCellColor
end if
Members_MemberName = rs("M_NAME")
Members_MemberID = rs("MEMBER_ID")
Members_MemberPostsThisMonth = rs("T_POSTS")
Response.Write " <tr>" & vbNewLine & _
" <td align=""center"" bgcolor=""" & CColor & """><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" & _
rank & "</font></td>" & vbNewLine & _
" <td bgcolor=""" & CColor & """><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" & vbNewLine
if strUseExtendedProfile then
Response.Write " <span class=""spnMessageText""><a href=""pop_profile.asp?mode=display&id=" & Members_MemberID & """ title=""View " & ChkString(Members_MemberName,"display") & "'s Profile""" & dWStatus("View " & ChkString(Members_MemberName,"display") & "'s Profile") & ">"
else
Response.Write " <span class=""spnMessageText""><a href=""JavaScript:openWindow3('pop_profile.asp?mode=display&id=" & Members_MemberID & "')"" title=""View " & ChkString(Members_MemberName,"display") & "'s Profile""" & dWStatus("View " & ChkString(Members_MemberName,"display") & "'s Profile") & ">"
end if
Response.Write ChkString(Members_MemberName,"display") & "</a></span></font></td>" & vbNewLine & _
" <td align=""center"" bgcolor=""" & CColor & """><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" & _
Members_MemberPostsThisMonth & "</font></td>" & vbNewLine & _
" </tr>" & vbNewLine
rank = rank + 1
intI = intI + 1
if intI = 2 then intI = 0
rs.MoveNext
Wend
rs.Close
Response.Write " </table>" & vbNewLine & _
" </td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" </table>" & vbNewLine & _
" <br><br>" & vbNewLine & _
" <table border=""0"" width=""300"" cellspacing=""0"" cellpadding=""0"" align=""center"">" & vbNewLine & _
" <tr>" & vbNewLine & _
" <td bgcolor=""" & strTableBorderColor & """>" & vbNewLine & _
" <table border=""0"" width=""100%"" cellspacing=""1"" cellpadding=""3"">" & vbNewLine & _
" <tr>" & vbNewLine
Response.Write " <td align=""center"" bgcolor=""" & strHeadCellColor & """><b><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strHeadFontColor & """>Rank</font></b></td>" & vbNewLine
Response.Write " <td align=""center"" bgcolor=""" & strHeadCellColor & """><b><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strHeadFontColor & """>Member Name</font></b></td>" & vbNewLine
Response.Write " <td align=""center"" bgcolor=""" & strHeadCellColor & """><b><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strHeadFontColor & """>Posts Last Month</font></b></td>" & vbNewLine & _
" </tr>" & vbNewLine
thisMonth = dateadd("m",-1,Now())
strStartDate = doublenum(Year(thisMonth)) & doublenum(Month(thisMonth)) & "01000000"
nextMonth = dateadd("m",+1,thisMonth)
strEndDate = doublenum(Year(nextMonth)) & doublenum(Month(nextMonth)) & "01000000"
strSql = " SELECT TOP 15 R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, COUNT(R_AUTHOR) AS T_POSTS FROM "
strSql = strSql & "(SELECT R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, R_DATE FROM (" & strMemberTablePrefix &"MEMBERS INNER JOIN " & strTablePrefix & "REPLY ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "REPLY.R_AUTHOR) WHERE R_DATE >= '" & strStartDate & "' AND R_DATE < '" & strEndDate & "'" & filter & vbCrLf
strSql = strSql & " UNION " & VbCrLf
strSql = strSql & "SELECT T_AUTHOR, M_NAME, MEMBER_ID, M_POSTS, T_DATE FROM (" & strMemberTablePrefix & "MEMBERS INNER JOIN " & strTablePrefix & "TOPICS ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "TOPICS.T_AUTHOR) WHERE T_DATE >= '" & strStartDate & "' AND T_DATE < '" & strEndDate &"'" & filter & ") As MyView "
strSql = strSql & "GROUP BY R_AUTHOR, M_NAME, MEMBER_ID, M_POSTS ORDER BY Count(R_AUTHOR) DESC, M_POSTS DESC;"
' Response.Write strSql & "<BR>"
intI = 0
rank = 1
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSql, strConnString
While Not Rs.EOF
'Response.Write rs("M_NAME") & " " & rs("T_POSTS") & " " & rs("MEMBER_ID") & "<br>"
if intI = 1 then
CColor = strAltForumCellColor
else
CColor = strForumCellColor
end if
Members_MemberName = rs("M_NAME")
Members_MemberID = rs("MEMBER_ID")
Members_MemberPostsThisMonth = rs("T_POSTS")
Response.Write " <tr>" & vbNewLine & _
" <td align=""center"" bgcolor=""" & CColor & """><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" & _
rank & "</font></td>" & vbNewLine & _
" <td bgcolor=""" & CColor & """><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" & vbNewLine
if strUseExtendedProfile then
Response.Write " <span class=""spnMessageText""><a href=""pop_profile.asp?mode=display&id=" & Members_MemberID & """ title=""View " & ChkString(Members_MemberName,"display") & "'s Profile""" & dWStatus("View " & ChkString(Members_MemberName,"display") & "'s Profile") & ">"
else
Response.Write " <span class=""spnMessageText""><a href=""JavaScript:openWindow3('pop_profile.asp?mode=display&id=" & Members_MemberID & "')"" title=""View " & ChkString(Members_MemberName,"display") & "'s Profile""" & dWStatus("View " & ChkString(Members_MemberName,"display") & "'s Profile") & ">"
end if
Response.Write ChkString(Members_MemberName,"display") & "</a></span></font></td>" & vbNewLine & _
" <td align=""center"" bgcolor=""" & CColor & """><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" & _
Members_MemberPostsThisMonth & "</font></td>" & vbNewLine & _
" </tr>" & vbNewLine
rank = rank + 1
intI = intI + 1
if intI = 2 then intI = 0
rs.MoveNext
Wend
rs.Close
Response.Write " </table>" & vbNewLine & _
" </td>" & vbNewLine & _
" </tr>" & vbNewLine & _
" </table>" & vbNewLine & _
" <br><br>" & vbNewLine
WriteFooter
Response.End
%>
I did not give so much attention to identation so forgive me if some </tr> misses...  Is up to you link to this page from wherever you want (inc_header.asp )
I've also added in pop_profile.asp below Total Posts also Posts This Month (maybe someone could be interested): In two steps:
Step 1: around line 170 find
'## Forum_SQL
strSql = "SELECT " & strMemberTablePrefix & "MEMBERS.MEMBER_ID"
strSql = strSql & ", " & strMemberTablePrefix & "MEMBERS.M_NAME"
strSql = strSql & ", " & strMemberTablePrefix & "MEMBERS.M_USERNAME"
strSql = strSql & ", " & strMemberTablePrefix & "MEMBERS.M_EMAIL"
strsql = strsql & ", " & strMemberTablePrefix & "MEMBERS.M_FIRSTNAME"
Right above add:
'########### GET POSTS THIS MONTH
thisMonth = Now()
strStartDate = doublenum(Year(thisMonth)) & doublenum(Month(thisMonth)) & "01000000"
nextMonth = dateadd("m",+1,thisMonth)
strEndDate = doublenum(Year(nextMonth)) & doublenum(Month(nextMonth)) & "01000000"
Dim strConn, conn, thisMonthMemberPosts
memb_id = Request.QueryString("id")
strSql = " SELECT COUNT(R_AUTHOR) AS T_POSTS FROM "
strSql = strSql & "( SELECT R_AUTHOR FROM " & strTablePrefix & "REPLY "
strSql = strSql & "WHERE R_AUTHOR = " & memb_id & " AND R_DATE >= '" & strStartDate & "' AND R_DATE < '" & strEndDate & "'" & vbCrLf
strSql = strSql & " UNION ALL " & VbCrLf
strSql = strSql & " SELECT T_AUTHOR FROM " & strTablePrefix & "TOPICS "
strSql = strSql & "WHERE T_AUTHOR = " & memb_id & " AND T_DATE >= '" & strStartDate & "' AND T_DATE < '" & strEndDate & "')"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSql, strConnString
While Not Rs.EOF
thisMonthMemberPosts = rs("T_POSTS")
rs.MoveNext
Wend
rs.Close
'########### END GET POSTS THIS MONTH
Step 2: around line 600 find
Response.Write " <tr>" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """ align=""right"" nowrap valign=""top""><b><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>Total Posts: </font></b></td>" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" & ChkString(intTotalMemberPosts, "display") & "<br /><font size=""" & strFooterFontSize & """>[" & strMemberPostsperDay & strPosts & " per day]<br /><a href=""search.asp?mode=DoIt&MEMBER_ID=" & rs("MEMBER_ID") & """>Find all non-archived posts by " & chkString(rs("M_NAME"),"display") & "</a></font></font></td>" & vbNewLine & _
" </tr>" & vbNewLine
Right after, before the end if add:
Response.Write " <tr>" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """ align=""right"" nowrap valign=""top""><b><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>Posts This Month: </font></b></td>" & vbNewLine & _
" <td bgColor=""" & strPopUpTableColor & """><font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """>" & ChkString(thisMonthMemberPosts, "display") & "<br /></td>" & vbNewLine & _
" </tr>" & vbNewLine
Hope it works for you too! Bye |
Never say D'oh! http://www.blanksheetmusic.net |
Edited by - cicciput on 30 June 2004 15:18:43 |
 |
|
|
Topic  |
|
|
|