Getting weekly data from sql

Question

I have a table which has a date column ands values according to dates.Normally if i want to select the data between two dates we use the BETWEEN syntax but it brings each day between those 2 values i have given.Is it possible to change it so it brings the dates with sopme interval i have given? For example if do a simple

SELECT * FROM METABLE WHERE DATE BETWEEN '2012-06-11' AND '2012-07-11'

It should bring me the dates as:

2012-06-11 myValue Stuff 
2012-06-12 myValue Stuff 
.
.
.
2012-07-11 myValue Stuff.

IS it possible to change it to,

2012-06-11 myValue Stuff 
2012-06-18 myValue Stuff 
2012-06-25 myValue Stuff 
.
.
.
Until my end date.

So this gives me my weekly values. How can i achieve that?

Answer 1

try this:

you could use group by with MIN() and SUM() functions

SELECT min(date),sum(col1),sum(col2) 
FROM   METABLE 
WHERE  [DATE] BETWEEN '2012-06-11' AND '2012-07-11'
group by datepart(ww,[DATE])

Click here for demo

EDIT1:

As per your comment, if you want to retrieve only one day from a week. Here as per your example in question, its Monday. So I am using set datefirst 1 to set it as Monday

set datefirst 1
select t.* 
from METABLE t
join
  (SELECT min([date]) as dt
   FROM METABLE 
   group by datepart(ww,[date]))a
on t.[date]=a.dt   

 set datefirst 7

Demo2