CODEWITHSUNDEEP
sql-serversql-server-2008-r2
RookieStacker
RookieStacker

Reputation: 37

Get latest record for each day for last n days using MS Sql Server

CurrencyId  LeftCurrencyId RightCurrencyId    ExchangeRateAt            ExchangeRate 
 1             1             5                2013-06-27 00:51:00.000    39.0123 
 2             3             5                2013-06-26 01:54:00.000    40.0120 
 3             1             5                2013-06-26 00:51:00.000    49.0143 
 4             3             5                2013-06-25 14:51:00.000    33.3123 
 5             3             5                2013-06-25 06:51:00.000    32.0163
 6             1             5                2013-06-25 00:08:00.000    37.0123

I need latest record for each day for last n days based on combination of leftcurrencyid and rightcurrencyid.

Upvotes: 1

Views: 2066

Answers (3)

valex
valex

Reputation: 24134

Here 7 in the end is the last N days parameter (7 in this example)

with T1 as
(
select t.*,
   cast(floor(cast([ExchangeRateAt] as float)) as datetime) as DatePart,  
   ROW_NUMBER() OVER (
          PARTITION BY [LeftCurrencyId],
                       [RightCurrencyId],
                       cast(floor(cast([ExchangeRateAt] as float)) as datetime)
          ORDER BY [ExchangeRateAt] DESC
     ) RowNumber
from t
), T2 as 
(
  select *,
  ROW_NUMBER() OVER (PARTITION BY [LeftCurrencyId],
                                  [RightCurrencyId]
                     ORDER BY DatePart DESC
                    ) as RN  
  from T1 where RowNumber=1
) 

select [CurrencyId],
       [LeftCurrencyId],
       [RightCurrencyId],
       [ExchangeRateAt],
       [ExchangeRate],
       DatePart  
from T2 where RN<=7

SQLFiddle demo

Upvotes: 0

Ian Preston
Ian Preston

Reputation: 39566

Here's one option:

with TopPerDay as
(
  select *
    , DayRank = row_number() over (partition by LeftCurrencyId, RightCurrencyId, cast(ExchangeRateAt as date)
                                   order by ExchangeRateAt desc)
   from ExchangeRate
)
select CurrencyId,
  LeftCurrencyId,
  RightCurrencyId ,
  ExchangeRateDay = cast(ExchangeRateAt as date),
  ExchangeRateAt ,
  ExchangeRate
from TopPerDay
where DayRank = 1
order by LeftCurrencyId,
  RightCurrencyId,
  ExchangeRateDay

SQL Fiddle with demo.

It groups by LeftCurrencyId, RightCurrencyId, and ExchangeRateAt day without the time component, then takes the latest record in the day for all those groups.

You don't mention whether you want N days back is from the present day or an unspecified date, but you can add this using a WHERE clause when selecting from the ExchangeRate table in the CTE definition.

Upvotes: 4

Dhaval
Dhaval

Reputation: 2861

Here are my two cents

Select ExchangeRateAt , * from Table1 where ExchangeRateAt in (Select max(ExchangeRateAt) from Table1 Group by  cast( ExchangeRateAt as Date))
Order by ExchangeRateAt

Upvotes: 0

Related Questions