Matching dates with conditions and filtering in a table

Question

So I have a tables like this

id | date1   |date2   |value  
1  | 1/1/2014|1/1/2004|100  
1  | 1/1/2014|1/1/2004|100  
1  | 1/1/2014|1/1/2009|200  
1  | 1/1/2014|1/1/2009|200   
1  | 1/1/2014|1/1/2016|300  
1  | 1/1/2015|1/1/2004|100  
1  | 1/1/2015|1/1/2004|100  
1  | 1/1/2015|1/1/2009|200   
1  | 1/1/2015|1/1/2009|200    
1  | 1/1/2015|1/1/2016|300   
1  | 1/1/2017|1/1/2004|100  
1  | 1/1/2017|1/1/2004|100  
1  | 1/1/2017|1/1/2009|200  
1  | 1/1/2017|1/1/2009|200   
1  | 1/1/2017|1/1/2016|300 

NOTE: Value col values could be random

How do I get the rows with date1>=date2 over various ids.

Essentially, if date1 is 2014 then I want to get just the row where date1-date2 is minimum but not negative.

The result for the example table would look like this:

id | date1   |date2   |value  
1  | 1/1/2014|1/1/2009|200  
1  | 1/1/2015|1/1/2009|200  
1  | 1/1/2017|1/1/2016|300

I hope I explained it well, if not, feel free to ask.

I am using MS SQL SERVER 2014

so far I have tried:

select id,date1,min(date2) over (partition by id,date1 order by id, date1) date2, value   
from tblA

This gives me:

id | date1   |date2   |value  
1  | 1/1/2014|1/1/2004|100  
1  | 1/1/2014|1/1/2004|100  
1  | 1/1/2014|1/1/2004|200  
1  | 1/1/2014|1/1/2004|200   
1  | 1/1/2014|1/1/2004|300  
1  | 1/1/2015|1/1/2004|100  
1  | 1/1/2015|1/1/2004|100  
1  | 1/1/2015|1/1/2004|200   
1  | 1/1/2015|1/1/2004|200    
1  | 1/1/2015|1/1/2004|300   
1  | 1/1/2017|1/1/2004|100  
1  | 1/1/2017|1/1/2004|100  
1  | 1/1/2017|1/1/2004|200  
1  | 1/1/2017|1/1/2004|200   
1  | 1/1/2017|1/1/2004|300 

Here is a link to fiddle with the table

Answer 1

I think what you want to do is rank the data based upon the smallest difference between the dates, per year - but you only need to consider any rows where date1 is greater that date2. For this, I would personally use a CTE to filter and rank the results before returning it like so:

WITH results
AS(
  SELECT id, date1, date2, value
       , RANK() OVER (PARTITION BY id, date1 
                ORDER BY DATEDIFF(DAY, date1, date2) DESC) AS rnk
    FROM tblA
   WHERE date1 >= date2
)
SELECT DISTINCT id, date1, date2, value
  FROM results
 WHERE rnk = 1;

You can find the results on dbfiddle.

For more information see the official documentation of RANK and CTE.