SQL Server Performance

Retriving values month by month from ms sql

Discussion in 'General DBA Questions' started by vinay789, Jul 14, 2009.

  1. vinay789 New Member

    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.
  2. moh_hassan20 New Member

    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

  3. vinay789 New Member

    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
  4. moh_hassan20 New Member

    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

  5. gregj New Member

    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]

Share This Page