Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Expression problem displaying rolling weekly/daily/monthly average Expand / Collapse
Author
Message
Posted Tuesday, May 04, 2010 8:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 9:52 AM
Points: 2, Visits: 95
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...
Post #915381
Posted Tuesday, May 04, 2010 10:46 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, October 28, 2013 8:10 AM
Points: 822, Visits: 1,198
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.
Post #915477
Posted Tuesday, May 04, 2010 2:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 20, 2012 9:52 AM
Points: 2, Visits: 95
Thanks a lot, I figured it out by using first/last day of month
Post #915636
Posted Tuesday, May 04, 2010 4:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #915713
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse