One of the many nice things about the current iterations of SQL since s05 is the inclusion of Common Table Expressions (CTE). A CTE is a 'derived table', which means that it can replace the temporary tables that we have used on occasion as well as functioning as a temporary View. Because these are restricted to the query you do not have to worry about their existence once the query is gone.
One of my favorite tricks with a CTE is to use it for a list of weeks, which I can then join with tables that have dates to sort them. This allows me to do an outer join and include weeks that have no data in the physical table itself. I wish I could point you to the discussion that stirred this idea, but I cannot find it anymore...
Example:
DECLARE @startDate DateTime = '9/1/10'
DECLARE @endDate DateTime = '8/31/11'
DECLARE @startOfWeek DateTime = DATEADD(day, -(Datepart(dw, @startDate) - 1), @startDate)
With Weeks (StartOfWeek, EndOfWeek, weekNumber, yearFor) AS
( select
@startOfWeek as StartOfWeek,
DATEADD(DAY, 6, @startOfWeek) as EndOfWeek,
DATEPART(WEEK, @startOfWeek),
DATEPART(YEAR, @startOfWeek)
UNION ALL
select
DATEADD(DAY, 7, StartOfWeek),
DATEADD(DAY, 7, EndOfWeek),
DATEPART(WEEK, DATEADD(day, 7, StartOfWeek)),
DATEPART(YEAR, DATEADD(day, 7, StartOfWeek))
from
Weeks
where
EndOfWeek <= @endDate
),
BillingSummary(weekNumber, yearFor, ClientId, TotalHours) AS
( Select
DATEPART(WEEK, DateFor) As weekNumber,
DATEPART(YEAR, DateFor) As YearFor,
ClientId,
SUM(HoursBilled) AS TotalHours
From Billing
Where DateFor >= @startDate And DateFor <= @endDate
Group By ClientId, DATEPART(YEAR, DateFor),DATEPART(WEEK, DateFor)
)
Select W.StartOfWeek, W.EndOfWeek,
IsNull(S.ClientId,'') AS ClientId, IsNull(S.TotalHours,0) As TotalHours
From Weeks W
Left Outer Join BillingSummary S ON W.yearFor = S.yearFor AND W.weekNumber = S.weekNumber
Order By W.startOfWeek Desc
Hope this helps...