Week Groupings

Hi all,

I have this code below and it appears to work, but I wondered if there was a better way.

Basically I have a table with some sales. The amount sold is stored in a varchar (for a very stupid reason) called ticketNo, the date of the sale is stored in txDate (thankfully this is a date).

We need to report out the total amount sold for a period in weekly groupings with the week starting on Monday. If a week starts within the period specified but does not finish we extend the period to include the whole week.

As I said the code below appears to work (no I have not tested extensively), but it looks like it will be pretty resouce intensive.

Any suggestions?

declare @minTxDate DateTime
declare @maxTxDate DateTime

set @minTxDate = '02-Jul-2007'
set @maxTxDate = '31-Aug-2007'

declare @StartOfYear DateTime
declare @LocalMaxTxDate DateTime
declare @WeekDay int
declare @StartWeek int
declare @EndWeek int

set @StartOfYear = '01-Jan-' + Convert(varchar(4),Year(getDate()))
set @WeekDay = DatePart(weekday,@StartOfYear)
set @StartWeek = DatePart(week, @minTxDate)
set @EndWeek = DatePart(week, @maxTxDate)

set @LocalMaxTxDate = DateAdd(d,10,@maxTxDate)

set DATEFIRST 1
select
DateAdd(d,((DatePart(week, tmpSale.txDate)-1)*7)+@WeekDay-1, @StartOfYear) 'FirstDayOfWeek',
DatePart(week, tmpSale.txDate) 'WeekOfSale',
COALESCE(sum (Convert(decimal, ticketNo)),0) 'TonnesSold'
from
(select
Sale.txDate,
Sale.ticketNo
from Sale
where Sale.txDate between @minTxDate and @LocalMaxTxDate)
tmpSale
where DatePart(week, tmpSale.txDate) between @StartWeek and @EndWeek
group by DatePart(week, tmpSale.txDate),
DateAdd(d,((DatePart(week, tmpSale.txDate)-1)*7)+@WeekDay-1, @StartOfYear)