August 15, 2008 at 2:45 am
I am trying to get a calculation for monthly average number of tickets but I cannot seem to get the right results.
The start date will always be 8/15/2007 – the end date is a parameter field which can always change, in this example the end date is 8/1/2008.
In order to get the daily average w/in a month – I need to count the number of days within each month and then get the average from the total tickets per month.
August 15, 2008 at 7:14 am
Either create a table with the number of days per month, or a CTE at the beginning of the query, or use a calendar table. Then you can use that in your query.
I recommend building a calendar table. You can use it for this, and for a ton of other things.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 15, 2008 at 11:36 am
-
August 15, 2008 at 12:00 pm
...
August 15, 2008 at 1:03 pm
That datediff calculation is using the 15 Aug start date for all of it's calculations. Thus, it adds the days for each month to the total for the prior month.
Calendar table is going to be better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 17, 2008 at 2:37 am
No need for calendar table.
This question is also asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108802
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply