title: LAG / OVER / PARTITION / ORDER BY using conditions - SQL Server 2017
body: <hr>
  
  <p>I have a table that looks like this: </p>
  
  <pre><code>Date            AccountID       Amount
  2018-01-01      123             12
  2018-01-06      123             150
  2018-02-14      123             11
  2018-05-06      123             16
  2018-05-16      123             200
  2018-06-01      123             18
  2018-06-15      123             17
  2018-06-18      123             110
  2018-06-30      123             23
  2018-07-01      123             45
  2018-07-12      123             116
  2018-07-18      123             60
  </code></pre>
  
  <p>This table has multiple dates and IDs, along with multiple Amounts. For each individual row, I want grab the last Date where Amount was over a specific value for that specific AccountID. I have been trying to use the LAG( Date, 1 ) in combination with several variatons of CASE and OVER ( PARTITION BY AccountID ORDER BY Date ) statements but I've had no luck. Ultimately, this is what I would like my SELECT statement to return.</p>
  
  <pre><code>Date            AccountID       Amount          LastOverHundred
  2018-01-01      123             12              NULL
  2018-01-06      123             150             2018-01-06
  2018-02-14      123             11              2018-01-06
  2018-05-06      123             16              2018-01-06
  2018-05-16      123             200             2018-05-16
  2018-06-01      123             18              2018-05-16
  2018-06-15      123             17              2018-05-16
  2018-06-18      123             110             2018-06-18
  2018-06-30      123             23              2018-06-18
  2018-07-01      123             45              2018-06-18
  2018-07-12      123             116             2018-07-12
  2018-07-18      123             60              2018-07-12
  </code></pre>
  
  <p>Any help with this would be greatly appreciated.</p>