sql - selecting greatest in group +N if greatest is within a range -

my goal relevant rate changes vendor. means need recent rate change, plus if rate change in last month, need previous rate(s) until rate outside of month.

i have sample data demo (this relating vendors , dates pay rate changes)

vendorid rateeffectivedate 2        2011-06-01 2        2012-03-15 2        2014-05-15 1        2010-07-01 1        2011-03-15 1        2013-07-01 

what select greatest effective date each vendorid + if row within last month, select next lower value.

in case, today being 2014-05-29 result:

vendorid rateeffectivedate 2        2012-03-15 2        2014-05-15 1        2013-07-01 

this way can calculate pay rates previous rate on course of month instead of using "newest". granted, newest work of time, edge case eluding me.

what have far is:

select vendorid, max(rateeffectivedate) rateeffectivedate @ratechanges group vendorid 

which gets me greatest date each vendorid

vendorid rateeffectivedate 2        2014-05-15 1        2013-07-01 

but i'm missing previous date when pay rate changed vendorid=2. while don't anticipate rate changing more once month, could. suspect sql might not place this, or need use loops accomplish feat.

i assume loops work best, don't know how "select" rows table this... http://msdn.microsoft.com/en-us/library/ms178642.aspx

i've looked @ this: how make faster greatest-n-per-group query?

but cannot figure out how previous date. can compare , dates above @date, count them add 1, i'm not sure that.

any suggestions helpful, thanks

---

edit:

this how solve in pseudo

id int firstofmonth datetime := -- value row := first row of table while row not null     id := row.vendorid             take row         move next     while row not null , row.rateeffectivedate > firstofmonth , row.vendorid = id      while row not null , row.vendorid == id         move next 

if want measure change in rates, why not use lag() function?

the following gets want 2 columns (and can use additional logic):

select rc.* (select vendorid, rateeffectivedate,              lag(rateeffectivedate) on (partition vendorid order rateeffectivedate) prevred       @ratechanges      ) rc rateeffectdate >= dateadd(month, -1, getdate()); 

if don't 2 columns, can use lead() following logic:

select rc.vendorid, rc.rateeffectivedate (select vendorid, rateeffectivedate,              lead(rateeffectivedate) on (partition vendorid order rateeffectivedate) nextred       @ratechanges      ) rc nextred null or nextred >= dateadd(month, -1, getdate()); 

edit:

as note, can implement these correlated subqueries or cross apply. instance, last is:

select rc.vendorid, rc.rateeffectivedate (select vendorid, rateeffectivedate,              (select top 1 rc2.rateeffectivedate               @ratechanges rc2               rc2.vendorid = rc.vendorid ,                     rc2.rateeffectivedate > rc.rateeffectivedate               order rc2.rateeffectivedate              ) nextred       @ratechanges rc      ) rc nextred null or nextred >= dateadd(month, -1, getdate());