title: SQL sub select if exists
body: <p>I am using SQL Server 2012. I have two tables to hold orders for products. Order which has a received date and OrderItem which has a price and order id fk. </p>
  
  <p>I am trying to write a query get all orders within a date range, group them by the date, and then sum all the order items price to get the total of all orders for that date.</p>
  
  <p>I have this working. Now I want to add another column to select the difference between the total price for that day, and 7 days ago. If there are no orders 7 days ago then the column should be null.</p>
  
  <p>So at the moment I have the below query:</p>
  
  <pre><code>select cast(o.ReceivedDate as date) as OrderDate, 
  coalesce(count(orderItems.orderId), 0) as Orders,
  coalesce(sum(orderItems.Price), 0) as Price
  from [Order] o
  
  left outer join (
          select o.Id as orderId, sum(ot.Price) as Price
          from OrderItem ot
          join [Order] o on ot.OrderId = o.Id
          where o.ReceivedDate &gt;= @DateFrom and o.ReceivedDate &lt;= @DateTo
          group by o.Id
  ) as orderItems on o.Id = orderItems.orderId
  
  where o.ReceivedDate &gt;= @DateFrom and o.ReceivedDate &lt;= @DateTo
  group by cast(o.ReceivedDate as date)
  order by cast(o.ReceivedDate as date) desc
  </code></pre>
  
  <p>So how can I add my other column to this query? I need to do something like:</p>
  
  <pre><code>//pseudo 
  if o.RecievedDate - 7 exists then orderItems.Price - Price from 7 days ago else null
  </code></pre>
  
  <p>But I am not sure how to do this? I have created a sqlfiddle to help explain <a href="http://sqlfiddle.com/#!6/8b837/1" rel="nofollow">http://sqlfiddle.com/#!6/8b837/1</a></p>
  
  <p>So from my sample data what I want to achieve is results like this:</p>
  
  <pre><code>|  ORDERDATE | ORDERS | PRICE |  DIFF7DAYS  |
  ---------------------------------------------
  | 2013-01-25 |      3 |    38 |          28 |
  | 2013-01-24 |      1 |    12 |        null |
  | 2013-01-23 |      1 |    10 |        null |
  | 2013-01-22 |      1 |    33 |        null |
  | 2013-01-18 |      1 |    10 |        null |
  | 2013-01-10 |      1 |     3 |         -43 |
  | 2013-01-08 |      2 |    11 |        null |
  | 2013-01-04 |      1 |     1 |        null |
  | 2013-01-03 |      3 |    46 |        null |
  </code></pre>
  
  <p>As you can see, the 25th has a order 7 days ago so the difference is shown. The 24th doesn't so null is displayed.</p>
  
  <p>Any help would be much appreciated.</p>