SQL Server Select last 4 weeks from now and same period from last year only

I have tried searching fo开发者_开发问答r a soln to this but have been unable yet to find one.

I need to select the last 4 weeks of data from todays date which is not the issue as I just do a date >= Dateadd(mm, -1, getdate()). However I also want the same 4 weeks of data from the previous year. But I dont want (for example) June 1-30 2010 and June 1-30 2011, I would need

June 30th (thursday) 2011 and 4 weeks prior AND July 1st and four weeks prior as july 1st was the thursday in the same week from the prev year.

so 8 weeks of data would be returned.

Thanks for the help!

You can use some more DATEADD() goodness to go back to the previous year:

where theDate >= DATEADD(mm, -1, GETDATE())    
OR
    (theDate <= DATEADD(week,-52,convert(datetime,GETDATE()))
     and
     theDate >= DATEADD(mm,-1,DATEADD(week,-52,convert(datetime,GETDATE()))))

Subtracting 52 weeks from 6/30/2011 returns 7/1/2010 as you requested... Then using your original subtraction of 1 month from there for the lower bound.

You could also switch the whole thing to use weeks...

where theDate >= DATEADD(week, -4, GETDATE())    
OR
    (theDate <= DATEADD(week,-52,convert(datetime,GETDATE()))
     and
     theDate >= DATEADD(week,-56,convert(datetime,GETDATE())))

you may do a serial of ORed BETWEEN conditions:

select
  ...
from
  ...
where
  1=1
  or date between Dateadd(mm, -2, getdate()) and Dateadd(mm, -1, getdate())
  or date between Dateadd(mm, -11, getdate()) and Dateadd(mm, -10, getdate())
order by
  date

did i understand right?

Last four weeks sales data:

with cte as (
    select case when row_number() OVER (ORDER BY DATEPART(wk,CreatedDate) DESC) = 2 then 'last 1st Week'
                when row_number() OVER (ORDER BY DATEPART(wk,CreatedDate) DESC) = 3 then 'last 2nd Week'
                when row_number() OVER (ORDER BY DATEPART(wk,CreatedDate) DESC) = 4 then 'last 3rd Week'
                when row_number() OVER (ORDER BY DATEPART(wk,CreatedDate) DESC) = 5 then 'last 4th Week'  
                else 'last 5th plusweek'
           end as weeks,
    sum(amt) as week_wise_sale,
    row_number() OVER (ORDER BY DATEPART(wk,CreatedDate) DESC) AS [row number]
    from samt 
    group by DATEPART(wk,CreatedDate)
) select * from cte where [ROW NUMBER] > 1