Retriving values month by month from ms sql

Hi
I am new bie to mssql server 2000.I am developing a report projectusing pagination and in my database there are lot of date records iwant to select first time current month records dates only and whenuser clicks next or previous button in pagination i want to selectprevious month or next month records dates only.I am sending querywhich i want to modify please help me to solve this problem.
"select COUNT(date),date,country from advertisementhits where transactionid='9P853908BJ2391528521"' GROUP BY date,country"
Thanks in advance.

you can create stored procedure for pagination and set it as a source for your report
for sql 2000
http://ehockeycoach.com/devgenuity/Articles/SQL/SQL2000Pagination.aspx
for sql005
http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx

Hi
Thanks for your reply. I have done pagination using jsp calendar my problem is with query please tell me how to write the query to retrive records by month

here is example:
suppose page length is 10 and next page to get is 60 ( the next page can be controlled from application by next , previous logic)
so total = 10 *60 = 600
order date by orderDate
the query may be:
use adventureworks
go
SELECT * FROM
(
SELECT TOP 10 * FROM
(
SELECT TOP 600 SalesOrderID, OrderDate, Status
From Sales.SalesOrderHeader
order by OrderDate ASC
) as AllPages
Order by OrderDate DESC
) as CurrentPage
Order by OrderDate ASC
you can adapt that logic for your case
in ado.net is very very simple and you need not such logic using dataset capability , and i suppose jsp may have built pagination service !!.
have a look to: http://www.jguru.com/forums/view.jsp?EID=1364445

How about this?

select
COUNT(*) [Hits],
right(convert(varchar(11),date,113),8) [Month],
country [Country],
year(date)*100+month(date) [Sort]
from advertisementhits
where transactionid='9P853908BJ2391528521'
GROUP BY
right(convert(varchar(11),date,113),8),
country,
year(date)*100+month(date)
order by
[Sort]