Results 1 to 7 of 7

Thread: Week Groupings

  1. #1
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618

    Lightbulb Unanswered: 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?
    Code:
    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)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Doesn't this expression:
    Code:
    where Sale.txDate between @minTxDate and @LocalMaxTxDate)
    mean you don't need this expression:
    Code:
    where DatePart(week, tmpSale.txDate) between @StartWeek and @EndWeek
    ?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Not entirely.

    The first where clause it limiting the records we do the datepart on but does not get the exact records for matching because of the week grouping issue.

    The second where clause takes the limited number of records and uses the DatePart to limit it to the exact records.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok. Why not then adjust your start and end dates to the start date of the first week and the end date of the last week? Wouldn't make much of a difference as the first filter gets you pretty close anyway so you aren't forcing a big scan.

    I don't expect the query to be resource intensive - quite the opposite given the right indexes.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2003
    Location
    Christchurch, New Zealand
    Posts
    1,618
    Well my thinking was,... say you have 100,000 records in that table but for the period you are interested in there are only 1500.

    The first query would get you down to your 1500+(the week overlap) records then you would run the datepart function on only the subset of records and not the full 100,000 records.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes, which is why it is not a major optimisation. It would save passing over those 1, 500 records a second time when you could have eliminated them in the first where clause. Not a huge improvement but your query is not very complex and there is not much more that can be improved.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually - you might want to check the execution plan. It appears that the date range and week range are evaluated at the same time. As such you would defo get an improvement by working out the exact dates and filtering on those.

    Also if this is the entire SQL statement than this is a good candidate for a covering index of (txDate, ticketNo) in that order. If you aren't sure of the tradeoff of maintaining an index vs the benefits of a covering index then have a little google around and see if it is applicable.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •