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