Compare Monday's data to previous Mondays in SQL Server

Question

I am trying to figure out how to compare the current day's data to the same data from a week ago, 2 weeks, etc. Let's say I have a table called "Order" with 2 columns:

Order table
-----------
OrderID int identity
OrderDate datetime

If today, is Monday, I would like to be able to compare the number of orders from today to the previous Mondays for an entire year. Is this possible with a single SQL Server query? I'm using SQL 2008 if it makes a difference.

Answer 1

Try

SELECT [ColumnsYouWant]
FROM [OrderTable]
WHERE datepart(weekday, OrderDate) = datepart(weekday, getdate()) 
   AND OrderDate >= dateadd(yyyy, -1, getdate())

Answer 2

select CAST (OrderDate  as date) as [Date], COUNT(*)
from Orders
where OrderDate  > DATEADD(YEAR,-1, getdate()) 
     and DATEPART(DW,OrderDate ) = DATEPART(DW,GETDATE())
group by CAST (OrderDate  as date)

Answer 3

This gives you Monday order counts by week number:

select year(OrderDate) as Year, 
    DATEPART(WEEK, OrderDate) as Week, 
    COUNT(*) as MondayOrderCount
from Order
where DATEPART(WEEKDAY, OrderDate) = 2
group by year(OrderDate), DATEPART(WEEK, OrderDate)
order by Year, Week