Expression problem displaying rolling weekly/daily/monthly average

Question

Post reply

Expression problem displaying rolling weekly/daily/monthly average

niccolem

Newbie

Points: 6
More actions
May 4, 2010 at 8:59 am

#221160

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...

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

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

Dan.Humphries SSChampion Points: 12125 More actions · Answer 1

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.

niccolem Newbie Points: 6 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004470 More actions · Answer 3

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)