How to count blog entries for each month? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to count blog entries for each month?

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2005-10-26 : 11:23:05

I'm writing a blog program and in this example I want to return a selection which shows past months and how many entries they each contain.

In other words it needs to return something like:
200510, October 2005, 72
200509, September 2005, 27
200508, August 2005, 19
200507, July 2005, 47
200506, June 2005, 63
200505, May 2005, 14

This is the query I have so far.

SELECT Distinct(YearMonth) AS YearMonth, MonthYear,COUNT(*) AS QtyLines FROM
(
SELECT Convert(char(6),Newsdate,112) AS YearMonth,
	Datename(mm,NewsDate)+' '+cast(datepart(yy,newsdate) AS char) AS MonthYear
FROM Mynews
) x
GROUP BY YearMonth,MonthYear
ORDER BY YearMonth DESC

It works, but can it be improved?

Thanks
Mark

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-01 : 21:53:08

it what way you want to improve it ?

[KH]

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2005-11-02 : 00:00:28

How about this?

SELECT Convert(char(6),Newsdate,112) AS YearMonth,
Datename(mm,NewsDate)+' '+cast(datepart(yy,newsdate) AS char) AS MonthYear, COUNT(*) AS QtyLines
FROM Mynews
GROUP BY Convert(char(6),Newsdate,112), Datename(mm,NewsDate)+' '+cast(datepart(yy,newsdate) AS char)
ORDER BY Convert(char(6),Newsdate,112) DESC

mark1504
Posting Yak Master

103 Posts

Posted - 2005-11-02 : 11:53:14

Great! Exactly the same result. There's no processing difference according to the execution plan, but at least you managed it in one select, and that had stumped me.

Thanks
Mark

CASE CLOSED

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources