1

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

  • All of the rows in your attempt match your requirement of date1 >= date2. Your requirement must be missing some information. – Michael Kutz Dec 7 '18 at 21:13
  • @MichaelKutz made an edit to update the requirement for clarity – SQLserving Dec 7 '18 at 21:19
2

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.

  • this wouldnt work if the VALUE is random. It does not always follow that trend – SQLserving Dec 7 '18 at 22:13
  • 1
    You did not explain that in your question - I have adjusted my answer accordingly. – Mr.Brownstone Dec 7 '18 at 22:15
  • 1
    I updated the question. Thanks for the fix! In your answer the date1>=date2. Other then that, works for me! – SQLserving Dec 7 '18 at 22:22
  • Thanks, I have adjusted my answer to include that. – Mr.Brownstone Dec 7 '18 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.