title: Is there more efficient way of grouping by date in SQL Server 2008
body: <p>I have to create a report which has AccountSegment as rows and a 2-week date range as column header.  The column values will be a count of the number of records in the table having the associated segment/date range.</p>
  
  <p>So the desired output looks something like this:</p>
  
  <pre><code>AcctSeg  4/9/12-4/20/12   4/23/12-5/4/12   5/7/12-5/18/12
  Segment1       100             200              300
  Segment2       110             220              330
  Segment3       120             230              340
  </code></pre>
  
  <p>The following query does what I want, but just looks so inefficient and ugly. I was wondering if there is a better way to accomplish the same thing:</p>
  
  <pre><code>SELECT
      AccountSegment = S.Segment_Name,
      '4/9/2012 - 4/20/2012' = SUM(CASE WHEN date_start BETWEEN '2012-04-09' AND '2012-04-20' THEN 1 END),
      '4/23/2012 - 5/4/2012' = SUM(CASE WHEN date_start BETWEEN '2012-04-23' AND '2012-05-04' THEN 1 END),
      '5/7/2012 - 5/18/2012' = SUM(CASE WHEN date_start BETWEEN '2012-05-07' AND '2012-05-18' THEN 1 END),
      '5/21/2012 - 6/1/2012' = SUM(CASE WHEN date_start BETWEEN '2012-05-21' AND '2012-06-01' THEN 1 END),
      '6/4/2012 - 6/15/2012' = SUM(CASE WHEN date_start BETWEEN '2012-06-04' AND '2012-06-15' THEN 1 END),
      '6/18/2012 - 6/29/2012' = SUM(CASE WHEN date_start BETWEEN '2012-06-18' AND '2012-06-29' THEN 1 END),
      '7/2/2012 - 7/13/2012' = SUM(CASE WHEN date_start BETWEEN '2012-07-02' AND '2012-07-13' THEN 1 END),
      '7/16/2012 - 7/27/2012' = SUM(CASE WHEN date_start BETWEEN '2012-07-16' AND '2012-07-27' THEN 1 END),
      '7/30/2012 - 8/10/2012' = SUM(CASE WHEN date_start BETWEEN '2012-07-30' AND '2012-08-10' THEN 1 END)
  FROM
      dbo.calls C
      JOIN dbo.accounts a ON C.parent_id = a.id
      JOIN dbo.accounts_cstm a2 ON a2.id_c = A.id
      JOIN dbo.Segmentation S ON a2.[2012_segmentation_c] = S.Segment_Num
  WHERE
      c.deleted = 0 
  GROUP BY
      S.Segment_Name
  ORDER BY
      MIN(S.Sort_Order)
  </code></pre>