Creating a blog-like chronological list to track user posts by month
One of the cool things everyone likes about blogs is that they let you navigate through a user's submissions chronologically, usually by month. Most, like .TEXT, even show how many posts a user's made for that month.
It's a cool little feature people really adhere to, making using them easy and fun.
Here's a T-SQL stored procedure I wrote that provides returns a recordset that you can easily use in your apps to pull this type of user experience. I use it in
the "Familiar Faces" community photo gallery app I built and use on
my site. It basically uses some of SQL Server's date-specific system functions to display field data of type SMALLDATETIME.
When bound to a list control (in this case a DataList), it renders the following:
CREATE PROCEDURE GetPersonalGalleryArchive
(
@UserID INT
)
AS
SELECT DATENAME(mm,DateOfPost) + ' ' + DATENAME(yy,DateOfPost) AS [MonthYear],COUNT(BlogPostID) AS [MonthlyPosts],CONVERT(SMALLDATETIME,CONVERT(VARCHAR(4),MONTH(DateOfPost)) +
'/1/' + CONVERT(VARCHAR(6),YEAR(DateOfPost))) AS [LinkDate]
FROM BlogPosts
WHERE UserID = @UserID
GROUP BY DATENAME(mm,DateOfPost) + ' ' + DATENAME(yy,DateOfPost),CONVERT(SMALLDATETIME,CONVERT(VARCHAR(4),MONTH(DateOfPost)) + '/1/' + CONVERT(VARCHAR(6),YEAR(DateOfPost)))
ORDER BY [LinkDate] DESC
GO
Just FYI....it uses the following database table schema:
-- this table is used by the SPROC CREATE TABLE BlogPosts
(
BlogPostID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
UserID INT NOT NULL, -- links to a membership table
DateOfPost SMALLDATETIME DEFAULT GETDATE() NOT NULL
)
You could also easily bind the recordset to a vertical-reading DataList or Repeater, for the blog effect that we're so used to. :)
Have fun!