Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    101

    Unanswered: get the row counts for each day going back to 6 months (was "query help")

    I have a proc to get the rowcounts for the given date range.
    I have to get the row counts for each day going back to 6 months on the table.

    With this proc i can get one day's row couts.. i need to loop through for all dates.

    Please can someone get me the code for this.



    create proc p_rowcounts

    @Date1 datetime,
    @Date2 datetime

    SELECT
    count (*) as 'Number of Rows', @Date1 as Date
    FROM
    Table1 (nolock)
    WHERE ModifyTime >= @Date1 and ModifyTime < @Date2

    thanks for the help.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd do it as:
    Code:
    CREATE PROC p_rowcounts
       @Date1		datetime = NULL
    ,  @Date2		datetime = NULL
    AS
    
    IF @Date1 IS NULL SET @Date1 = GetDate()
    IF @Date2 IS NULL SET @Date2 = DateAdd(month, -6, Convert(CHAR(10), @Date1, 121))
    
    SELECT
       Count (*) AS 'Number of Rows'
    ,  Convert(DATETIME, Convert(CHAR(10), ModifyTime, 121)) AS Date 
       FROM Table1 (nolock)
       WHERE ModifyTime BETWEEN @Date2 AND @Date1
       GROUP BY Convert(CHAR(10), ModifyTime, 121)
    
    RETURN
    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, i think sskris wants one count per date in the range
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That query ought to give one count per day in the range. I think you're hinting that you'd like to see rows with zeros for a count for days with no data, which I see as wasteful and poor practice.

    If you have code that relies on zeros, you can certainly go to added trouble to make the zeros appear, but in my mind you'd be much better off to fix the code instead of writing SQL to cater to the problems in it.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •