How to group by week (7 days) in SQL Server

Question

I would like to show the week column in 7 days increment. Below is my desired output example. Is there a way to write in SQL Server?

Week           Football  soccer  baseball
10/01 - 10/07      1        2      3
10/08 - 10/14      4        5      6
10/15 - 10/21      5        8      0

Here is my attempted script:

CREATE TABLE #TEMP
(
DT CHAR(10), 
TO_DT CHAR(10),  
BALLs VARCHAR(100),
PRODUCT INT,
Week INT
) 

DECLARE 
@DT DATETIME, 
@TO_DT   DATETIME,
@i INT

BEGIN
SET @_DT     = '10/01/2012' 
SET @TO_DT   = DATEADD(DAY,7,@_DT)
set @i=1


WHILE (@DT <= '12/31/2013')


BEGIN
INSERT INTO #TEMP (DT, TO_DT, BALLs, PRODUCT, week)

SELECT
CONVERT(CHAR(10),@DT,101)                   AS FROM_DT, 
CONVERT(CHAR(10),DATEADD(DAY,-1,@TO_DT),101)    AS TO_DT,
BALLS,
COUNT(PRODUCT)                  AS PRODUCT,
@I                                              AS WEEK
FROM #TABLE
GROUP BY BALLS

SET @DT = DATEADD(DAY,7,@DT)
SET @TO_DT   = DATEADD(DAY, 7,@TO_DT)
set @i = @i + 1
END


SELECT * FROM #TEMP

DROP TABLE #TEMP

END

Answer 1

DATEDIFF(WK, 0, ) should do the trick.

SELECT
    DATEDIFF(WK, 0, MyDate)
    , COUNT(*)
FROM (VALUES
('2013-01-01')
, ('2013-01-02')
, ('2013-01-03')
, ('2013-01-04')
, ('2013-01-05')
, ('2013-01-06')
, ('2013-01-07')
) AS X(MyDate)
GROUP BY DATEDIFF(WK, 0, MyDate)

EDIT:

With date formatting it goes like this:

SELECT
    CONVERT(VARCHAR(5), DATEADD(WK, WK, 0), 103) + ' - ' + CONVERT(VARCHAR(5), DATEADD(DD, 6, DATEADD(WK, WK, 0)), 103)
    , Cnt
FROM (
    SELECT
        DATEDIFF(WK, 0, MyDate) AS WK
        , COUNT(*) AS Cnt
    FROM (VALUES
    ('2013-01-01')
    , ('2013-01-02')
    , ('2013-01-03')
    , ('2013-01-04')
    , ('2013-01-05')
    , ('2013-01-06')
    , ('2013-01-07')
    ) AS X(MyDate)
    GROUP BY DATEDIFF(WK, 0, MyDate)
) AS X