title: SQL Server group by week query
body: <p>Please see my query below and the results</p>
  
  <pre><code>(SELECT count(*) as Count, p.regionid, a.territory,
  CASE WHEN DATEPART(day,p.prodate) &lt; 8 THEN '1' 
    ELSE CASE WHEN DATEPART(day,p.prodate) &lt; 15 then '2' 
      ELSE CASE WHEN  DATEPART(day,p.prodate) &lt; 22 then '3' 
        ELSE CASE WHEN  DATEPART(day,p.prodate) &lt; 29 then '4'     
         ELSE '5'   
        END
      END
    END
  END as WeekNumber 
  from pros p 
  inner join
  GTS_Account a 
  on a.locationGTSNo = p.BillToCustNo
    where a.account like '%abc%' and
    p.prodate between '2014-02-01' and '2014-02-28' 
  group by p.ProDate,p.regionid,a.territory)
  </code></pre>
  
  <p>Results</p>
  
  <pre><code>╔═══════╦══════════╦═══════════╦════════════╗
  ║ Count ║ regionid ║ territory ║ WeekNumber ║
  ╠═══════╬══════════╬═══════════╬════════════╣
  ║     3 ║ LTL      ║         2 ║          1 ║
  ║    23 ║ RDFS     ║         2 ║          1 ║
  ║    10 ║ VOLUME   ║         2 ║          1 ║
  ║    22 ║ RDFS     ║         2 ║          1 ║
  ║    10 ║ VOLUME   ║         2 ║          1 ║
  ║     1 ║ EXP      ║         2 ║          2 ║
  ║     3 ║ LTL      ║         2 ║          2 ║
  ║    25 ║ RDFS     ║         2 ║          2 ║
  ║    11 ║ VOLUME   ║         2 ║          2 ║
  ║     1 ║ BRK      ║         2 ║          2 ║
  ║     6 ║ VOLUME   ║         2 ║          2 ║
  ║     1 ║ BRK      ║         2 ║          3 ║
  ║     4 ║ EXP      ║         2 ║          3 ║
  ║     1 ║ LTL      ║         2 ║          3 ║
  ║    20 ║ RDFS     ║         2 ║          3 ║
  ║     4 ║ EXP      ║         2 ║          4 ║
  ║     4 ║ LTL      ║         2 ║          4 ║
  ║    30 ║ RDFS     ║         2 ║          4 ║
  ║    15 ║ VOLUME   ║         2 ║          4 ║
  ╚═══════╩══════════╩═══════════╩════════════╝
  </code></pre>
  
  <p>This shows the data for each day in the week, I just want it to show the total for each region/per week not per day. Thanks</p>
  
  <p>3-13-14
  I would like to show the Month and Year column for this data, please the query below, it should show only one row for each region/week.</p>
  
  <pre><code>SELECT convert(nvarchar,DATEPART(YYYY,p.prodate))  + ' ' +
  CASE WHEN DATEPART(month,p.prodate) = 1 THEN 'JAN' 
      WHEN DATEPART(month,p.prodate) = 2 THEN 'FEB' 
      WHEN DATEPART(month,p.prodate) = 3 THEN 'MAR' 
      WHEN DATEPART(month,p.prodate) = 4 THEN 'APR' 
      WHEN DATEPART(month,p.prodate) = 5 THEN 'MAY' 
      WHEN DATEPART(month,p.prodate) = 6 THEN 'JUN' 
      WHEN DATEPART(month,p.prodate) = 7 THEN 'JUL' 
      WHEN DATEPART(month,p.prodate) = 8 THEN 'AUG' 
      WHEN DATEPART(month,p.prodate) = 9 THEN 'SEP' 
      WHEN DATEPART(month,p.prodate) = 10 THEN 'OCT' 
      WHEN DATEPART(month,p.prodate) = 11 THEN 'NOV' 
      WHEN DATEPART(month,p.prodate) = 12 THEN 'DEC' 
      ELSE '6'
      END AS MONTHnAME,
  count(*) as Count, p.regionid, 
  CASE WHEN DATEPART(day,p.prodate) &lt; 8 THEN '1' --+ ' ' +convert(nvarchar,DATEPART(mm,p.prodate))
       WHEN DATEPART(day,p.prodate) &lt; 15 then '2' --+ ' ' +convert(nvarchar,DATEPART(mm,p.prodate))
      WHEN  DATEPART(day,p.prodate) &lt; 22 then '3' --+ ' ' +convert(nvarchar,DATEPART(mm,p.prodate))
      WHEN  DATEPART(day,p.prodate) &lt; 29 then '4'  --+ ' ' +convert(nvarchar,DATEPART(mm,p.prodate))   
      ELSE '5' + ' ' +convert(nvarchar,DATEPART(mm,p.prodate)) 
  END as WeekNumber,a.territory,'rrts' from pros p inner join GTS_Account a on a.locationGTSNo = p.BillToCustNo
    where a.account like '%rrts%' and
    p.prodate between '2014-02-01' and '2014-02-28' group by 
    CASE 
              WHEN Datepart(day, p.prodate) &lt; 8 THEN '1' 
              WHEN Datepart(day, p.prodate) &lt; 15 THEN '2' 
              WHEN Datepart(day, p.prodate) &lt; 22 THEN '3' 
              WHEN Datepart(day, p.prodate) &lt; 29 THEN '4' 
              ELSE '5' 
            END
    ,p.prodate,a.territory, p.regionid
    order by p.regionid, weeknumber
  </code></pre>
  
  <p>MONTHnAME   Count   regionid    WeekNumber  territory   (No column name)
  2014 FEB    1   BRK     1   0002    rrts
  2014 FEB    1   BRK     2   0002    rrts
  2014 FEB    1   BRK     2   0002    rrts
  2014 FEB    1   BRK     3   0002    rrts
  2014 FEB    2   BRK     4   0002    rrts
  2014 FEB    1   BRK     4   0002    rrts
  2014 FEB    1   DED     3   0002    rrts
  2014 FEB    3   EXP     1   0002    rrts
  2014 FEB    1   EXP     1   0002    rrts
  2014 FEB    2   EXP     1   0002    rrts
  2014 FEB    3   EXP     1   0002    rrts
  2014 FEB    2   EXP     1   0002    rrts
  2014 FEB    1   EXP     2   0002    rrts
  2014 FEB    1   EXP     2   0002    rrts
  2014 FEB    2   EXP     2   0002    rrts
  2014 FEB    1   EXP     2   0002    rrts
  2014 FEB    4   EXP     3   0002    rrts
  2014 FEB    1   EXP     3   0002    rrts
  2014 FEB    6   EXP     3   0002    rrts
  2014 FEB    3   EXP     3   0002    rrts
  2014 FEB    7   EXP     3   0002    rrts
  2014 FEB    1   EXP     4   0002    rrts
  2014 FEB    4   EXP     4   0002    rrts
  2014 FEB    2   EXP     4   0002    rrts
  2014 FEB    3   EXP     4   0002    rrts</p>