Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Snitz Forums 2000 MOD-Group
 MOD Add-On Forum (W/O Code)
 count post per day for member		  New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ez4arab
Junior Member

479 Posts
Posted - 05 June 2003 :  20:39:41  Show Profile  Visit Ez4arab's Homepage
How can I make sql selection for member post per day e.g.
Month June
1st = 1 post(s)
2nd = 0 post(s)
3rd = 6 post(s)
4th = 9 post(s)
Up to end of month

30th = 4 post(s)


the selection for total post per day this is sql 


strSql = "SELECT R_AUTHOR, MEMBER_ID, M_NAME, M_DATE, M_POSTS, M_TITLE, M_LEVEL, M_LASTPOSTDATE, M_COUNTRY, COUNT(R_AUTHOR) AS T_POSTS FROM "
strSql = strSql & "(SELECT R_AUTHOR, MEMBER_ID, M_NAME, M_DATE, M_POSTS, M_TITLE, M_LEVEL, M_LASTPOSTDATE, M_COUNTRY, R_DATE  FROM (" & strMemberTablePrefix &"MEMBERS INNER JOIN " & strTablePrefix & "REPLY ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "REPLY.R_AUTHOR) WHERE R_DATE " & sqlperiod & " AND M_NAME <> '" & strAnonyName & "'" & vbCrLf
strSql = strSql & " UNION " & VbCrLf
strSql = strSql & "SELECT T_AUTHOR, MEMBER_ID, M_NAME, M_DATE, M_POSTS, M_TITLE, M_LEVEL, M_LASTPOSTDATE, M_COUNTRY, T_DATE  FROM (" & strMemberTablePrefix & "MEMBERS INNER JOIN " & strTablePrefix & "TOPICS ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "TOPICS.T_AUTHOR) WHERE T_DATE " & sqlperiod & " AND M_NAME <> '" & strAnonyName & "') As MyView "
strSql = strSql & "GROUP BY R_AUTHOR, MEMBER_ID, M_NAME, M_DATE, M_POSTS, M_TITLE, M_LEVEL, M_LASTPOSTDATE, M_COUNTRY ORDER BY Count(R_AUTHOR) DESC, MEMBER_ID ASC;"


the problem that i encounter is how to display the members and days

Ez4arab web site Ez4arab |
Edited by - Ez4arab on 05 June 2003 20:42:56

GauravBhabu
Advanced Member

4288 Posts
Posted - 05 June 2003 :  23:01:07  Show Profile
For snitz if you GroupBy Date you will not get the total of daily posts by a member.

Try this


SELECT Count(FORUM_TOPICS.TOPIC_ID) AS PostCount, 
Left([FORUM_TOPICS.T_DATE],8) AS DatePosted, FORUM_TOPICS.T_AUTHOR
FROM FORUM_TOPICS
GROUP BY Left([T_DATE],8), FORUM_TOPICS.T_AUTHOR
UNION
SELECT Count(FORUM_REPLY.REPLY_ID) AS PostCount, 
Left([FORUM_REPLY.R_DATE],8) AS DatePosted, FORUM_REPLY.R_AUTHOR
FROM FORUM_REPLY
GROUP BY Left([FORUM_REPLY.R_DATE],8), FORUM_REPLY.R_AUTHOR
Go to Top of Page

GauravBhabu
Advanced Member

4288 Posts
Posted - 05 June 2003 :  23:21:48  Show Profile
This will give you

NO Of POSTS
DATE
MEMBER_ID
MEMBER NAME


SELECT 
Count(FORUM_TOPICS.TOPIC_ID) AS PostCount, 
Left([FORUM_TOPICS.T_DATE],8) AS DatePosted, 
FORUM_TOPICS.T_AUTHOR AS AuthorID, 
FORUM_MEMBERS.M_NAME
FROM FORUM_TOPICS 
INNER JOIN FORUM_MEMBERS ON FORUM_TOPICS.T_AUTHOR=FORUM_MEMBERS.MEMBER_ID
GROUP BY Left([FORUM_TOPICS.T_DATE],8), 
FORUM_TOPICS.T_AUTHOR,
FORUM_MEMBERS.M_NAME
UNION
SELECT 
Count(FORUM_REPLY.REPLY_ID) AS PostCount, 
Left([FORUM_REPLY.R_DATE],8) AS DatePosted, 
FORUM_REPLY.R_AUTHOR AS AuthorID, 
FORUM_MEMBERS.M_NAME
FROM FORUM_REPLY 
INNER JOIN FORUM_MEMBERS ON FORUM_REPLY.R_AUTHOR=FORUM_MEMBERS.MEMBER_ID
GROUP BY Left([FORUM_REPLY.R_DATE],8), 
FORUM_REPLY.R_AUTHOR,
FORUM_MEMBERS.M_NAME
ORDER BY AuthorID, DatePosted
Edited by - GauravBhabu on 05 June 2003 23:37:34
Go to Top of Page

Ez4arab
Junior Member

479 Posts
Posted - 06 June 2003 :  02:35:42  Show Profile  Visit Ez4arab's Homepage
will done GauravBhabu now I will continue built the script.
Ez4arab web site Ez4arab |
Go to Top of Page

GauravBhabu
Advanced Member

4288 Posts
Posted - 06 June 2003 :  07:04:33  Show Profile
Goodluck!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.34 seconds. Powered By: Snitz Forums 2000 Version 3.4.07