title: Max() Over Partition By with date conditions
body: <p>How do I get the following result  in yellow?</p>
  
  <p><a href="https://i.stack.imgur.com/Za3WP.png" rel="noreferrer"><img src="https://i.stack.imgur.com/Za3WP.png" alt="enter image description here"></a></p>
  
  <p>I want to find the maximum score over the previous year (excluding the current date) and partition by Name1 and Parent1</p>
  
  <p>I've tried the following which is not giving the desired result, it just returns the max with correct partition but across all dates.</p>
  
  <pre><code>select 
  [VDate]
  ,[Name1]
  ,[Parent1]
  ,[Score]
  ,max(case when [VDate] &gt; dateadd(year, -1, [VDate]) then [Score] else null end) over (partition by [Name1], [Parent1]) AS MaxScoreInPreviousLast12Months
  from [dbo].[Control]
  </code></pre>
  
  <p>Table Data:</p>
  
  <pre><code>CREATE TABLE Control
      ([VDate] datetime, [Name1] varchar(10), [Parent1] varchar(10), [Score] int);
  
  INSERT INTO Control ([VDate], [Name1], [Parent1], [Score])
  VALUES
  ('2018-08-01 00:00:00', 'Name1', 'Parent1', 80),
  ('2018-07-01 00:00:00', 'Name1', 'Parent1', 85),
  ('2018-06-01 00:00:00', 'Name1', 'Parent1', 90),
  ('2017-09-01 00:00:00', 'Name1', 'Parent1', 100),
  ('2017-08-01 00:00:00', 'Name1', 'Parent1', 95),
  ('2017-07-01 00:00:00', 'Name1', 'Parent1', 70),
  
  ('2018-08-01 00:00:00', 'Name2', 'Parent2', 80),
  ('2018-07-01 00:00:00', 'Name2', 'Parent2', 85),
  ('2018-06-01 00:00:00', 'Name2', 'Parent2', 90),
  ('2017-10-01 00:00:00', 'Name2', 'Parent2', 60),
  ('2017-08-01 00:00:00', 'Name2', 'Parent2', 95),
  ('2017-07-01 00:00:00', 'Name2', 'Parent2', 70),
  
  ('2018-08-01 00:00:00', 'Name3', 'Parent3', 80),
  ('2018-07-01 00:00:00', 'Name3', 'Parent3', 96),
  ('2018-06-01 00:00:00', 'Name3', 'Parent3', 90),
  ('2017-10-01 00:00:00', 'Name3', 'Parent3', 96),
  ('2017-08-01 00:00:00', 'Name3', 'Parent3', 99),
  ('2017-07-01 00:00:00', 'Name3', 'Parent3', 105)
  ;
  </code></pre>
  
  <p>This is for SQL Server 2016+</p>