title: SQL Server aggregate function without joining view to table
body: <p>My title is confusing, but I got no other way to describe it.</p>
  
  <p>I have a view that gives me one row based on certain criteria. Using this view I want to filter another table wich the records date must be equal or bigger than the value from the view. After that I need to sum all records of this table and subtract to a column in the view.</p>
  
  <p>An example:</p>
  
  <p>View</p>
  
  <pre><code>==========================
  Item | Value | LastDate
  ==========================
   A      10     2017-02-01
   B      5      2017-02-03
   C      0      NULL
  --------------------------
  </code></pre>
  
  <p><strong>Records</strong> table:</p>
  
  <pre><code>==========================
  Item | Value | Date
  ==========================
   A      20     2017-01-01
   A      -5     2017-02-04 
   A       5     2017-02-03 
   A      10     2017-02-04 
   B       5     2017-01-01 
   B       5     2017-02-21 
   B       5     2017-02-22 
   B      10     2017-02-23 
  --------------------------
  </code></pre>
  
  <p>Desired result</p>
  
  <pre><code>=======================================
  Item | Value | LastDate    | RecordsSum
  =======================================
   A      10     2017-02-01     10
   B      5      2017-02-03     20
   C      0      NULL           NULL
  ---------------------------------------
  </code></pre>
  
  <p>I've tried to use <a href="https://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server/" rel="nofollow noreferrer">Fabiano's</a> approach, however I get the records table data joined when I just want the sum.</p>
  
  <p>I could use another view to calculate the value and join it to the first view after, however this approach would make two queries to the database, making it inefficient since it will run against >1000k records.</p>
  
  <p>I know I might need to use a CTE, but I can't make it work.</p>
  
  <p>Thank you in advance for your help.</p>