Thread: Archive by Year and Month Query - MSSQL

Archive by Year and Month Query - MSSQL

Here is an example query to retrieve years, months, number of posts made. This kind of query usually used in blogs.

Select COUNT(id),DATEPART(year, publishDate) as year
,DATEPART(MONTH,publishDate) as month
from BlogPosts
where
publishDate<=getdate()
group by DATEPART(year, publishDate), DATEPART(MONTH,publishDate)
order by year desc,month desc