How to reference 2 weeks - 1 day within DATEADD() function?

Question

I have a dataset which shows currency spot rates on associated business days and would like to pull values for the 10 most recent data points prior to and including a reference date for EACH currency.

Data looks as follows:

AsOfDate    Currency    ExchangeRateUSD
9/11/1997   AED -1
9/11/1997   AUD 1.389178
9/11/1997   CAD 1.39125
9/11/1997   CHF 1.4774
9/11/1997   CZK 33.6755
9/11/1997   DKK 6.8203
9/11/1997   HKD 7.7455
9/11/1997   IDR 2943
9/11/1997   JPY 119.77
9/11/1997   KWD -1
9/11/1997   MXN 7.7855
9/11/1997   NOK 7.36715
9/11/1997   NZD 1.579654
9/11/1997   PHP 32.53
9/11/1997   SEK 7.7944
9/11/1997   SGD 1.511
9/11/1997   THB 35.475
9/11/1997   TWD 28.6125
9/11/1997   ZAR 4.698
9/12/1997   AED -1
9/12/1997   AUD 1.386001
9/12/1997   CAD 1.39325
9/12/1997   CHF 1.46475
9/12/1997   CZK 33.449
9/12/1997   DKK 6.7377
9/12/1997   HKD 7.746
9/12/1997   IDR 2933
9/12/1997   JPY 120.815
9/12/1997   KWD -1
9/12/1997   MXN 7.7765
9/12/1997   NOK 7.2844
9/12/1997   NZD 1.575175
9/12/1997   PHP 32.2
9/12/1997   SEK 7.65845
9/12/1997   SGD 1.514
9/12/1997   THB 36.225
9/12/1997   TWD 28.613
9/12/1997   ZAR 4.6865
9/15/1997   AED -1
9/15/1997   AUD 1.387829
9/15/1997   CAD 1.39185
9/15/1997   CHF 1.45175

With a reference date of 2/19/1999, I'd like to return the following data (example shows just two currencies, actual data returned would include all currencies):

    AsOfDate    Currency    ExchangeRateUSD
2/8/1999    EUR 0.888968
2/9/1999    EUR 0.884956
2/10/1999   EUR 0.883275
2/11/1999   EUR 0.885152
2/12/1999   EUR 0.886682
2/15/1999   EUR 0.889957
2/16/1999   EUR 0.894454
2/17/1999   EUR 0.890234
2/18/1999   EUR 0.891107
2/19/1999   EUR 0.901266
2/8/1999    JPY 113.71
2/9/1999    JPY 114.38
2/10/1999   JPY 114.52
2/11/1999   JPY 114.3
2/12/1999   JPY 114.305
2/15/1999   JPY 115.525
2/16/1999   JPY 118.175
2/17/1999   JPY 118.895
2/18/1999   JPY 119.82
2/19/1999   JPY 120.53

For a reference date of 2/25/1999, I'd like to return the following data (again, actual data would show all currencies):

AsOfDate    Currency    ExchangeRateUSD
2/12/1999   EUR 0.886682
2/15/1999   EUR 0.889957
2/16/1999   EUR 0.894454
2/17/1999   EUR 0.890234
2/18/1999   EUR 0.891107
2/19/1999   EUR 0.901266
2/22/1999   EUR 0.906454
2/23/1999   EUR 0.910705
2/24/1999   EUR 0.91295
2/25/1999   EUR 0.904895
2/12/1999   JPY 114.305
2/15/1999   JPY 115.525
2/16/1999   JPY 118.175
2/17/1999   JPY 118.895
2/18/1999   JPY 119.82
2/19/1999   JPY 120.53
2/22/1999   JPY 120.33
2/23/1999   JPY 121.195
2/24/1999   JPY 121.88
2/25/1999   JPY 120.1

As you can see, the number of days covered between the start and end dates will be different depending on the reference date, so I can't use the day DATEPART within DATEADD(). Using the compound 2weeks-1day WILL get me the appropriate range though.

Is there any way to use a compound DATEPART within DATEADD() or another way to do this?

EDIT - I missed a pivotal point...I'd like to do this for multiple currencies with multiple reference dates simultaneously. I have a table of reference dates (#referenceDates) that I'll be joining the exchange rates table (#exchangeRates) to and I'd like to use BETWEEN within a WHERE clause to pull the appropriate dates for each reference date, hence the need for compound DATEPART within DATEADD().

RichardTheKiwi RichardTheKiwi · Accepted Answer · 2012-12-06T20:11:57

For a reference date of 2/25/1999

SELECT TOP(10) *
  FROM DATA
 WHERE Currency = 'JPY'
   AND AsOfDate <= '19990225'
ORDER BY AsOfDate DESC;

For reference, if you actually want 2 weeks - 1 day, a naive expression:

DATEADD(D, +1, DATEADD(WK, -2, '19990225'))

But you're better to go with -13 days directly

DATEADD(D, -13, '19990225')

Or in your query (but it won't give you 10 data points if you have gaps in the days in your data):

SELECT *
  FROM DATA
 WHERE Currency = 'JPY'
   AND AsOfDate <= '19990225'
   AND AsOfDate >= DATEADD(D, -13, '19990225')
ORDER BY AsOfDate ASC;

How to reference 2 weeks - 1 day within DATEADD() function?

3 Answers