spiderlily spiderlily - 7 months ago 8
SQL Question

SQL Find Last Entry Closest to a Date

I am trying to filter the last entry in a table closet to a defined date and I am having difficulties. Any input is greatly appreciated. Thanks! I am running Microsoft SQL Server 2008.

Table:

code | account | date | amount
1 | 1234 | 2016-02-28 | 500
2 | 1234 | 2016-03-01 | 650
3 | 1234 | 2016-03-05 | 842
4 | 7890 | 2016-02-28 | 500
5 | 7890 | 2016-03-30 | 550


I want to select only entries with a date closest to March 31 ('2016-03-31'). In this example, the entry closest to 2016-03-31 for account 1234 is entry #3 and the entry closest to 2016-03-31 for account 7890 is entry #5. In other words, I want the last entry for all accounts equal to or before a date.

3 | 1234 | 2016-03-05 | 842
5 | 7890 | 2016-03-30 | 550

Answer

Most DBMSes (including MS SQL Server) support Analytical Functions:

select *
from
 (
   select *,
      row_number()                    -- create a ranking
      over (partition by account      -- for each account
            order by date desc) as rn -- based on descending dates
   from tab
   where date <= date '2016-03-31'
 ) dt
where rn = 1                          -- return the row with the "closest" date
Comments