Post reply

Compare totals on the day of week between two years

  • February 1, 2008 at 6:22 pm

    #186250

    I'm looking for some guidance on the best way to building this query or function or what ever is needed to achieve the results I'm looking for. I want to compare totals on any given day of the current year to the totals from a year ago. What makes this difficult is that I want to compare the totals based on the day of week. For example, lets say I have the results for today 2/1/2008 and the day of week is Friday. I want to compare the totals to the first Friday in Feb/2007 which was 2/2/2007. I think I may be making this too complicated but I'm not certain where to begin.

    Any help would be appreciated.

    Thanks!

  • February 1, 2008 at 7:54 pm

    #774511

    I was able to figure out a way to achieve the results I wanted. I used a few datepart functions and a derived query.

  • February 1, 2008 at 10:24 pm

    #774516

    Good work on solving it yourself 🙂

    You may think it seems a bit silly at first, but you may wish to consider creating a dates table (very similar to a tally or numbers table - look them up on this site). In it you could populate several years worth of dates and also have columns that contain information about that date. These columns could be

    Date

    DayOfWeek

    Year

    Month

    DayOfMonth

    DayOfYear

    WeekNumInMonth

    IsHoliday

    IsWeekend

    IsWeekday

    etc

    Several years worth of dates is only a few thousand rows - nothing at all really (maybe a couple of hundred kb tops). It will make the query you're trying to write VERY efficient. Make sure you create some appropriate indices on the dates table.

  • February 1, 2008 at 11:53 pm

    #774518

    For what it's worth - you might just care to compare it to the day 52 weeks ago

    dateadd(week,-52,getdate()) Should be the same day of the week...

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • February 2, 2008 at 8:17 am

    #774531

    Heh... gotta love the simple stuff. 🙂 Wonder if it works for leap years...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply