SQL Server - Select rows of a "max date" from a table (using ROW_NUMBER() OVER)

I often come across different requirements where I need to get the "top row" where certain criteria exists. It often applies to VAT Rates, Exchange Rates, even historical data where a different record may apply depending on the scenario. For this example, let's use VAT rates.

Let's create sample data using the following:

create table #myvattable (vatname nvarchar(50), effectivedate datetime)
insert #myvattable values('Rate A', '01/01/2009')
insert #myvattable values('Rate B', '01/01/2009')
insert #myvattable values('Rate C', '01/01/2009')
insert #myvattable values('Rate A', '01/02/2009')
insert #myvattable values('Rate B', '01/02/2009')
insert #myvattable values('Rate B', '01/02/2008')


So, we have several instances of the same "Rate" in the table that have different effective dates. Now, let's take this requirement:

I want to retrieve all VAT rates applicable for a certain date. How do we do this?


Step 1 - use a row_number() clause on your dataset:

select * from
(
select row_number() over (partition by a.vatname order by a.vatname, a.effectivedate desc) as rownum,
a.vatname, a.effectivedate
from #myvattable a
)
tbl



This simply puts a row_number clause on each row partitioning by the vatname. This returns a result set similar to this:

rownum vatname effectivedate
------ ------------- -----------------------
1 Rate A 2009-01-02 00:00:00.000
2 Rate A 2009-01-01 00:00:00.000
1 Rate B 2009-01-02 00:00:00.000
2 Rate B 2009-01-01 00:00:00.000
3 Rate B 2008-01-02 00:00:00.000
1 Rate C 2009-01-01 00:00:00.000


Ok, so what does this do for us? It effectively ranks the rows by each vatname so that we can see which ones are most applicable. Basically we want all the 1's.

Step 2 - Filter the rownums.
So our query becomes this:

select * from
(
select row_number() over (partition by a.vatname order by a.vatname, a.effectivedate desc) as rownum,
a.vatname, a.effectivedate
from #myvattable a
)
tbl
where rownum = 1


Ok, pretty much there now. The only step really that is left is... what if someone wants historical data? This query is fixed to the latest most applicable vatrate.


Step 3 - Make the date flexible

select * from
(
select row_number() over (partition by a.vatname order by a.vatname, a.effectivedate desc) as rownum,
a.vatname, a.effectivedate
from #myvattable a
where effectivedate <= @DATEVARIABLE )
tbl
where rownum = 1


So, @DATEVARIABLE could be a parameter/variable... or, even better, a field from another joined table.