title: Sum on case expression when working with dates
body: <p>I'm looking to create a view which will output the data in the following format</p>
  
  <pre><code>AgedPeriod   BillValue  Status
  &lt;1            35000     Outstanding 
  1-3           23386     Outstanding 
  3-6           5000      Outstanding
  </code></pre>
  
  <p>I can use the code below to SUM each case statement into a new column and I could name the column headings after the AgedPeriod listed above but even though the <code>SUMS</code> are right the format is wrong I would like to have the code below nested in another CASE statement that does not have to be GROUPED by <code>b.BILL_DATE</code> as grouping with the bill date defeats the purpose of my SUM. All attempts as using another CASE statement always bring the <code>b.BILL_DATE</code> out of the <code>SUM</code> and into the WHEN condition requiring it to be grouped.</p>
  
  <pre><code>SELECT 
  SUM(CASE WHEN (b.BILL_DATE &lt;= GetDate() 
               AND b.BILL_DATE &gt;= DateAdd(mm,-1, GetDate())) 
          THEN b.OUTSTANDING END),
  SUM(CASE WHEN (b.BILL_DATE &lt;= DateAdd(mm,-1, GetDate()) 
                AND b.BILL_DATE &gt;= DateAdd(mm,-3, GetDate())) 
           THEN b.OUTSTANDING END),
  SUM(CASE WHEN (b.BILL_DATE &lt;= DateAdd(mm,-3, GetDate()) 
                AND b.BILL_DATE &gt;= DateAdd(mm,-6, GetDate())) 
           THEN b.OUTSTANDING END) 
  FROM  dbo.Tables
  </code></pre>
  
  <p>I understand this may not be achievable with the route that I have taken at present but is there be any other way I can <code>SUM</code> the outstanding amount on each time period? I can deal with the status column (no advice needed there)</p>
  
  <p>I have added a table and some sample data and left a query to show how I would want the data split up but it would want it to be formatted as above (in a column)</p>
  
  <p><a href="http://sqlfiddle.com/#!3/e364d/1" rel="nofollow">Example on Sql Fiddle</a></p>
  
  <p>Thanks</p>