title: How do I exclude rows with a null value in the derived column?
body: <p>I am running a query where I am calculating the next value using the LEAD function. The problem is that I need to remove the rows that contain a NULL in this "next value" column. I can't do a <code>WHERE NextProductID IS NULL</code> because the column is calculated with the LEAD funciton.</p>
  
  <p>Example code:</p>
  
  <pre><code>SELECT BusinessEntityID, ProductID, LEAD(ProductID) OVER(PARTITION BY BusinessEntityID ORDER BY BusinessEntityID) AS NextProductID
  FROM Purchasing.ProductVendor
  ORDER BY BusinessEntityID, ProductID
  </code></pre>
  
  <p>I'm using AdventureWorks2014. Any help would be appreciated!</p>