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
date1 >= date2
. Your requirement must be missing some information.