CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Chris
Chris

Reputation: 1680

Select top and bottom 3 records by date for a variety of dates

I have a table with currency exchange rates formatted similar to the following:

asofdate    currency    spot    fwd1m   fwdyield    rank_
1999-03-26 00:00:00.000 EUR 0.93161900  0.93340000  1.001911725716199433459 1
1999-03-26 00:00:00.000 NOK 7.80300000  7.81630000  1.001704472638728694092 2
1999-03-26 00:00:00.000 CAD 1.51350000  1.51355000  1.000033036009250082590 3
1999-03-26 00:00:00.000 AUD 1.57728700  1.57713800  0.999905533996032427833 4
1999-03-26 00:00:00.000 NZD 1.86828600  1.86748300  0.999570194285029165770 5
1999-03-26 00:00:00.000 GBP 0.61728400  0.61665000  0.998972920082166393426 6
1999-03-26 00:00:00.000 SEK 8.33400000  8.32080000  0.998416126709863210943 7
1999-03-26 00:00:00.000 CHF 1.48480000  1.48007000  0.996814385775862068965 8
1999-03-26 00:00:00.000 JPY 120.09000000    119.60550000    0.995965525855608293779 9
1999-05-26 00:00:00.000 EUR 0.95438100  0.95662000  1.002346023233907632276 1
1999-05-26 00:00:00.000 NOK 7.85100000  7.86505000  1.001789580945102534708 2
1999-05-26 00:00:00.000 AUD 1.54154500  1.54147300  0.999953293611279592875 3
1999-05-26 00:00:00.000 CAD 1.47330000  1.47307000  0.999843887870766306930 4

From this table, I'd like to keep both the TOP and BOTTOM 3 records by rank (which is based on the fwdyield field) for each date.

Output would look like this, but would include every date present in the initial table:

asofdate    currency    spot    fwd1m   fwdyield    pos
1999-03-26 00:00:00.000 JPY 120.09000000    119.60550000    0.995965525855608293779 -1
1999-03-26 00:00:00.000 CHF 1.48480000  1.48007000  0.996814385775862068965 -1
1999-03-26 00:00:00.000 SEK 8.33400000  8.32080000  0.998416126709863210943 -1
1999-03-26 00:00:00.000 CAD 1.51350000  1.51355000  1.000033036009250082590 1
1999-03-26 00:00:00.000 NOK 7.80300000  7.81630000  1.001704472638728694092 1
1999-03-26 00:00:00.000 EUR 0.93161900  0.93340000  1.001911725716199433459 1

The challenge is in doing this for each date so as to avoid needing to use a loop/cursor. Thoughts?

Upvotes: 0

Views: 249

Answers (1)

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

This can be done through the use of some OLAP functions:

SELECT * -- only for the example, because I don't know what you need
FROM (SELECT a.*, ROW_NUMBER() OVER(PARTITION BY CAST(asOfDate as DATE) 
                                    ORDER BY fwdyield ASC) as ascRank,
                  ROW_NUMBER() OVER(PARTITION BY CAST(asOfDate as DATE) 
                                    ORDER BY fwdyield DESC) as descRank
      FROM Exchange a) b
WHERE ascRank < 4
      OR descRank < 4

Which yields the following from your sample data:

ASOFDATE    CURRENCY    SPOT    FWD1M   FWDYIELD    RANK    ASCRANK DESCRANK
March, 26 1999 00:00:00+0000    EUR 0.931618988514  0.9333999753    1.001911759377  1   9   1
March, 26 1999 00:00:00+0000    NOK 7.802999973297  7.816299915314  1.001704454422  2   8   2
March, 26 1999 00:00:00+0000    CAD 1.513499975204  1.513550043106  1.000033020973  3   7   3
March, 26 1999 00:00:00+0000    SEK 8.333999633789  8.320799827576  0.998416125774  7   3   7
March, 26 1999 00:00:00+0000    CHF 1.484799981117  1.480069994926  0.996814370155  8   2   8
March, 26 1999 00:00:00+0000    JPY 120.089996337891    119.605499267578    0.995965540409  9   1   9
May, 26 1999 00:00:00+0000  EUR 0.954380989075  0.956619977951  1.002346038818  1   4   1
May, 26 1999 00:00:00+0000  NOK 7.850999832153  7.86504983902   1.001789569855  2   3   2
May, 26 1999 00:00:00+0000  AUD 1.541545033455  1.541473031044  0.999953269958  3   2   3
May, 26 1999 00:00:00+0000  CAD 1.473299980164  1.473070025444  0.999843895435  4   1   4

(working SQL Fiddle example)

There's a couple of problems here, though:

  • Because asOfDate is a timestamp (an SQL Server DATETIME or DATETIME2), it needs to be cast/converted. This means it (probably) won't be able to use an index. I'm assuming this is supposed to be 'business day' (or similar), so converting it to a DATE type should be fine (and perhaps should be done anyways, for 'domain' reasons).
  • Your column names are a bit cryptic... what does fwd... stand for?

(As a side note, I notice the original rank column matches descRank - if this is already stored, you could obviously remove that particular calculated column.)

Upvotes: 2

Related Questions