Matching dates with conditions and filtering in a table 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
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 &gt;= 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.