title: Using LAG function to obtain value that is not necessarily in the previous row
body: <p>I have a question about the extent of the functionality of LAG. In the specific example I am dealing with I have a table similar to the following, although this is very simplified:</p>
  
  <pre><code>ID          Name             ItemID      VisitDate
  ----------- ---------------- ----------- ----------
  316         Name,Test        9           2016-11-01
  316         Name,Test        12          2016-11-01
  316         Name,Test        89          2018-03-09
  316         Name,Test        10          2018-03-09
  316         Name,Test        1           2018-03-09
  </code></pre>
  
  <p>My goal was to use LAG to have an additional column that showed the Most Recent VisitDate before the current one. So what I would like to see is </p>
  
  <pre><code>ID          Name             ItemID      VisitDate  LagDate
  ----------- ---------------- ----------- ---------- ----------
  316         Name,Test        9           2016-11-01 NULL
  316         Name,Test        12          2016-11-01 NULL
  316         Name,Test        89          2018-03-09 2016-11-01
  316         Name,Test        10          2018-03-09 2016-11-01
  316         Name,Test        1           2018-03-09 2016-11-01
  </code></pre>
  
  <p>However, I have not been able to find a way to get the table to show up this way. Initially I wrote the following code</p>
  
  <pre><code>SELECT
      TT.ID
     ,TT.Name
     ,TT.ItemID
     ,TT.VisitDate
     ,LAG(TT.VisitDate) OVER ( PARTITION BY TT.ID ORDER BY TT.VisitDate ) AS LagDate
  FROM
      @TestTable AS TT;
  </code></pre>
  
  <p>However, that returned the following table:</p>
  
  <pre><code>ID          Name             ItemID      VisitDate  LagDate
  ----------- ---------------- ----------- ---------- ----------
  316         Name,Test        9           2016-11-01 NULL
  316         Name,Test        12          2016-11-01 2016-11-01
  316         Name,Test        89          2018-03-09 2016-11-01
  316         Name,Test        10          2018-03-09 2018-03-09
  316         Name,Test        1           2018-03-09 2018-03-09
  </code></pre>
  
  <p>That's when I realized that the multiple ItemID values on a single date were causing an issue for me. So does anyone know of a way I can still use LAG on this table, but get the proper LagDate I am looking for on each row?</p>