Expression problem displaying rolling weekly/daily/monthly average

  • Hello:

    I have a series of reports that I need to display a weekly,daily average of records that passed/failed validation. I can correctly bring in my record count from my stored procedure.

    HOW can i correctly display via expression the weekly/daily/monthly average based on startdate parameter input.

    Report need to look like:

    Total Files Received

    Avg Files Per Day

    Avg Files Per Week

    All reports are dynamic and only show months data is available.

    Any help is greatly appreciated...

  • I would think you just need to break you date down into date parts and use that in you grouping.

    So lets say you have a record set with the type of item being sold, the date of sale and amount.

    to get daily average

    select type, Ave(amount)

    from sales

    group by type,DATEPART( dd, SaleDate )

    To get week average

    select type, Ave(amount)

    from sales

    group by type,DATEPART( WK, SaleDate )

    To get Month average

    select type, Ave(amount)

    from sales

    group by type,DATEPART( mm, SaleDate )

    I realize this is not a complete solution based on your request but it will hopefully point you in the right direction.

    Dan

    If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.

  • Thanks a lot, I figured it out by using first/last day of month

  • niccolem (5/4/2010)


    Thanks a lot, I figured it out by using first/last day of month

    Are you passing those as a parameter? If so, what happens if someone forgets some like the fact that 2008 was a leap year and they type 28 Feb as the last day of the month? Or what if someone can't remember that months like August have 31 days and they type 30 Aug???

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply