locked
Selecting last 52 weeks of data RRS feed

  • Question

  • I have sum up totals going back to 52 weeks. How would i be able to get only sales  from 52 weeks back? Here' are my Week fields. I know how to get the current week, which i set up as a variable @Current_week, but how do i go back 52 weeks. I tried @Current - 52, but i found it was only going as far back as the beggining of the year. I need it to go from 2010-2009 IE  ... 201003 , 201002, 201001, 200953, 200952 etc etc


    FYear          FWkLabel   FWKey       Week  
    2009           WK41 09    200941       41
    2009           WK42 09    200942       42 
    2010           WK41 10    201041       41
    2010           WK40 10    201040       40

    I also have date fields with these types of formats.

    DateKey 20090101
    CalendarDate  2010-12-25 00:00:00.000

    Please help!!
    Friday, February 12, 2010 4:04 PM

Answers

  • You can simply use DATEADD function with week as first argument using your Date field and go back 52 weeks.

    E.g.

    where datefield >=dateadd(week,-52, @Today) and datefield < @Tomorrow.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by LZee Friday, February 12, 2010 4:38 PM
    Friday, February 12, 2010 4:24 PM

All replies

  • You can simply use DATEADD function with week as first argument using your Date field and go back 52 weeks.

    E.g.

    where datefield >=dateadd(week,-52, @Today) and datefield < @Tomorrow.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by LZee Friday, February 12, 2010 4:38 PM
    Friday, February 12, 2010 4:24 PM
  • Try:

    select top (52) *
    from T
    where FWKey <= @Current_FWKey
    order by FWKey DESC;


    AMB
    Friday, February 12, 2010 4:33 PM
  • This worked just fine! thanks NOAM! 
    Friday, February 12, 2010 4:39 PM
  • Hi,

    The below query will give 52 weeks data...including leap years.

    select * from T where FWkey > cast(datepart(year,getdate() - 364) as char(4)) + cast(datepart(ww,getdate() - 364) as char(2))
    and FWkey <= cast(datepart(year,getdate()) as char(4)) + cast(datepart(ww,getdate()) as char(2))

    I have used current date as getdate()

    Thanks
    Gnana
    Gnanasekar Babu Note: Please click on the vote button if the answer helps you
    Friday, February 12, 2010 4:39 PM
  • Alejandro,

    Nice idea, but we also need a Year in the ordering.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog

    The FWKey that he's ordering by has the year incorporated into it... It's in YYYYWW format.
    --Brad (My Blog)
    Friday, February 12, 2010 5:05 PM
  • this gives me just what i want

       sum(case when CalendarDate >= dateadd(week,-1, @Last_Week_Date)  
                      then  Retailer_Fact.SR_Amount  else 0 end
                      ) AS WK_1 , 


    But i also need to go back 1yr

       sum(case when CalendarDate >= dateadd(week,-1, @Last_Week_Date)  
    and Year = YEAR(GETDATE())-1
                      then  Retailer_Fact.SR_Amount  else 0 end
                      ) AS LY_WK_1 , 

    Why wont this work? I just want to get the same info on sales for this time last year. (last week of last year)

    How would i go about this?

    do i need to trim the date, to exclude year, when i do the dateadd?
    Friday, February 12, 2010 6:31 PM
  • If you need to get last 52 weeks of sales for previous year, then obviously you need to use start date as last date of last year, e.g.

    declare @LastYear datetime --(date in 2008)
    set @LastYear = cast(year(getdate())-1 as char(4)) + '1231'
    select * from myTable where myDate <=@LastYear and myDate>=dateadd(week,-52,@LastYear)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, February 12, 2010 6:51 PM
  • It won't work because the first condition (CalendarDate>=DATEADD(...))  is SPECIFICALLY asking for dates that are within the last week, so that automatically eliminates items from last year.

    I think what you want is this:

    SUM(CASE
               WHEN CalendarDate>=DATEADD(week,-53,@Last_Week_Date)
                         AND CalendarDate<=DATEADD(week,-52,@Last_Week_Date)
               THEN Retailer_Fact.SR_Amount
               ELSE 0
            END) AS LY_WK_1

    You may need to change the >= or <= to just > or < depending on your data and your requirements.

    --Brad (My Blog)
    Friday, February 12, 2010 6:51 PM
  • Thanks for the info!


    I went ahead and created another variable for weekkey and was able to pull it up. Thanks!
    Friday, February 12, 2010 8:50 PM
  • It won't work because the first condition (CalendarDate>=DATEADD(...)) is SPECIFICALLY asking for dates that are within the last week, so that automatically eliminates items from last year.

    I think what you want is this:

    SUM(CASE
    WHEN CalendarDate>=DATEADD(week,-53,@Last_Week_Date)
    AND CalendarDate<=DATEADD(week,-52,@Last_Week_Date)
    THEN Retailer_Fact.SR_Amount
    ELSE 0
    END) AS LY_WK_1

    You may need to change the >= or <= to just > or < depending on your data and your requirements.

    --Brad (My Blog)

    Yep, I also think it's possibly the cause here.
    Friday, January 14, 2011 1:10 AM