title: How to group by week (7 days) in SQL Server
body: <p>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?</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here is my attempted script:</p>
  
  <pre><code>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 &lt;= '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
  </code></pre>