Group by MonthEnd

I have the following query which returns a sum of hours for each month
for a specific year.
select month(tDate) as month, sum(hours) as total from tablename
where Year(tDate) = 2005
group by month(tDate)
order by month
This works ok, however, is there a way to specify the month start date
and month end date for the grouping, much like it's possible to set the
first day of the w by setting DateFirst to 1=Monday?
With the query above, it totals hours from the 1st of the month to the
last day of the month. However, our month end date is the last Sunday
of the month and it's start date is the last Sunday of the previous
month + a day (i.e. Monday)
For example, our total hours for 2005 each month should be calculated
from the following dates:-
Jan: Mon 27 Dec 04 - Sun 30 Jan 05
Feb: Mon 31 Jan 05 - Sun 27 Feb 05
Mar: Mon 28 Feb - Sun 27 Mar
Apr: Mon 28 Mar - Sun 24 Apr
May: Mon 25 Apr - Sun 29 May
Jun: Mon 30 May - Sun 26 Jun
Jul: Mon 27 Jun - Sun 31 Jul
Aug: Mon 1 Aug - Sun 28 Aug
Sep: Mon 29 Aug - Sun 25 Sep
Oct: Mon 26 Sep - Sun 30 Oct
Nov: Mon 31 Oct - Sun 27 Nov
Dec: Mon 28 Nov - Sun 25 Dec
I suppose ideally i would need some kind of query to calculate the
monthly period based on the date and group it by that.
Any suggestions are most appreciated.
Thanks in advance.
Dan Williams.dan_williams@.newcross-nursing.com wrote:
> I have the following query which returns a sum of hours for each month
> for a specific year.
> select month(tDate) as month, sum(hours) as total from tablename
> where Year(tDate) = 2005
> group by month(tDate)
> order by month
> This works ok, however, is there a way to specify the month start date
> and month end date for the grouping, much like it's possible to set
> the first day of the w by setting DateFirst to 1=Monday?
>
I think your best course of action is to use an auxiliary calendar table, as
suggested in this article:
http://www.aspfaq.com/show.asp?id=2519
The benefit of using a calendar table as opposed to calculations is that it
will allow indexes on your date fields to be used, resulting in much
better-performing queries.
An auxiliary Numbers table can also be useful in other situations:
http://www.aspfaq.com/show.asp?id=2516
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||That's great. Think this will prove useful in a lot of my existing
code.
I mangaged to create a new calendar table with additional columns to
store a target month and target year column based on each date. I can
then do an inner join on this table based on my dates and group by the
target month.
I'm not entirely convinced of using additional memory to store a
numbers table, but if I come across any additional use of it, i'll be
sure to post. (I have since dropped the numbers table!)
Thanks for you help
Dan