| Well, Some time ago I wrote this nasty query to select the posts which are of the present month. CREATE PROCEDURE usp_GetCurrentPosts
AS
DECLARE @CurrentYear int
DECLARE @CurrentMonth int
SET @CurrentYear = YEAR(GETDATE())
SET @CurrentMonth = MONTH(GETDATE())
SELECT PostID, Title, Url, Description, DateCreated FROM Posts
WHERE @CurrentYear = YEAR(DateCreated) AND @CurrentMonth = MONTH(DateCreated) AND
Active = 1
GO Today I refactored it out to this: -- Get all the post not depending if it has the thread or not SELECT p.PostID, p.Url, p.Title,COUNT(t.ThreadID),p.DateCreated "Comments" FROM Posts p
LEFT OUTER JOIN Threads t ON p.PostID = t.PostID
WHERE
p.Active = 1
AND
CONVERT(CHAR(6), p.DateCreated, 112) = CONVERT(CHAR(6),GETDATE(), 112)
GROUP BY p.PostID,p.Url,p.Title,p.DateCreated Everyday you learn something new :)
powered by IMHO |
Print | posted @ Sunday, December 11, 2005 11:50 PM