title: Aggregate sales: Showing customers with past, but no current sales
body: <p>Brushing up on some sql... I'm trying to create a list of customers with sales aggregated per year (2005-2008), but will only show customers with sales in previous (2005-2007) yet no sales in 2008.</p>
  
  <p>I've built two queries... not sure which is more effective, but either way I cant seem to figure out how to implement the where clause correctly.</p>
  
  <p>Any help would be much appreciated!!</p>
  
  <p>Query 1:</p>
  
  <pre><code>select r.ResellerName,
  (select sum(rs.SalesAmount) from FactResellerSales rs
  where rs.ResellerKey = r.ResellerKey
  and rs.OrderDateKey &gt;=20050101 and rs.OrderDateKey &lt;20060101) '2005',
  
  (select sum(rs.SalesAmount) from FactResellerSales rs 
  where rs.ResellerKey = r.ResellerKey
  and rs.OrderDateKey &gt;=20060101 and rs.OrderDateKey &lt;20070101) '2006',
  
  (select sum(rs.SalesAmount) from FactResellerSales rs
  where rs.ResellerKey = r.ResellerKey
  and rs.OrderDateKey &gt;=20070101 and rs.OrderDateKey &lt;20080101) '2007',
  
  (select sum(rs.SalesAmount) from FactResellerSales rs
  where rs.ResellerKey = r.ResellerKey
  and rs.OrderDateKey &gt;=20080101 and rs.OrderDateKey &lt;20090101) '2008'
  
  From DimReseller r
  order by r.ResellerName ASC
  </code></pre>
  
  <p>Query 2:</p>
  
  <pre><code>Select r.ResellerName,
  SUM(case when OrderDateKey&gt;=20050101 and OrderDateKey&lt;20060101 then rs.SalesAmount else 0 end) '2005',
  SUM(case when OrderDateKey&gt;=20060101 and OrderDateKey&lt;20070101 then rs.SalesAmount else 0 end) '2006',
  SUM(case when OrderDateKey&gt;=20070101 and OrderDateKey&lt;20080101 then rs.SalesAmount else 0 end) '2007',
  SUM(case when OrderDateKey&gt;=20080101 and OrderDateKey&lt;20090101 then rs.SalesAmount else 0 end) '2008'
  
  from DimReseller r
  inner join FactResellerSales rs on rs.ResellerKey = r.ResellerKey
  Group by r.ResellerName
  Order by ResellerName ASC
  </code></pre>