RichardAtk
asked on
SQL View nearest date
Hi
I have a table full of dates and another table with exchange rates per company which only have a start date. Is there any way I can create a view and pick up the nearest date. For anything less that 01/01/2008 the nearest date would be this - for anything current and after 01/08/2016 it would be this date (until a later date is added.)
Table 1 has dates from 2004 - 2020 - Table 2 is multi company so needs to only bring the nearest exchange rate for company = US
Table 1 Date
11/02/2016
12/02/2016
Table 2
USA GBP 01/01/2008 0.625
USA GBP 01/03/2009 0.667
USA GBP 01/03/2010 0.625
USA GBP 01/04/2010 0.667
USA GBP 01/06/2011 0.621
USA GBP 03/05/2012 0.625
USA GBP 04/04/2016 0.69
USA GBP 01/08/2016 0.73
So would want the view to read
Date Rate
04/05/2012 0.625
11/02/2016 0.69
12/02/2016 0.69
12/02/2017 0.73
Thanks
I have a table full of dates and another table with exchange rates per company which only have a start date. Is there any way I can create a view and pick up the nearest date. For anything less that 01/01/2008 the nearest date would be this - for anything current and after 01/08/2016 it would be this date (until a later date is added.)
Table 1 has dates from 2004 - 2020 - Table 2 is multi company so needs to only bring the nearest exchange rate for company = US
Table 1 Date
11/02/2016
12/02/2016
Table 2
USA GBP 01/01/2008 0.625
USA GBP 01/03/2009 0.667
USA GBP 01/03/2010 0.625
USA GBP 01/04/2010 0.667
USA GBP 01/06/2011 0.621
USA GBP 03/05/2012 0.625
USA GBP 04/04/2016 0.69
USA GBP 01/08/2016 0.73
So would want the view to read
Date Rate
04/05/2012 0.625
11/02/2016 0.69
12/02/2016 0.69
12/02/2017 0.73
Thanks
ASKER
Hi Table 1 has dates from 2004 until 2020 just a big list of dates Table 2 has the 8 lines listed and I want to bring in the nearest date. Therefore anything less than the first date of 01/01/2008 would always be 0.625 and anything greater than the 01/08/2016 would always be 0.73. Guess if it's picking up the nearest date it's going to handle that anyway.
You can either use a separate query (I find this easier to test) or a sub query. Your separate query/subquery would return the max date that is <= the date you want the price for. Then you use the date value returned by the separate/subquery in a join to bring back the dollar amount.
Select Country, Code, Max(rateDate) From table2
Where rateDate <= [somedate argument]
Group by Country, code;
Then join table2 to this query on Country, Code, RateDate to retrieve the rate.
For 11/2/16, you would get USA GBP 04/04/2016 0.69
For 12/2/16 you would get the same record
This of course presumes you are using USA date format - m/d/yy Since none of the dates in the sample give a clear indication, the only clue I have is the sequence of the list. It is incorrect if it is m/d/y format but correct for d/m/y
As d/m/y, both dates would still return the same record but it would be - USA GBP 01/08/2016 0.73
I don't understand why your original post indicated that four records would be returned. It should never be more than 2- one for each input date.
Select Country, Code, Max(rateDate) From table2
Where rateDate <= [somedate argument]
Group by Country, code;
Then join table2 to this query on Country, Code, RateDate to retrieve the rate.
For 11/2/16, you would get USA GBP 04/04/2016 0.69
For 12/2/16 you would get the same record
This of course presumes you are using USA date format - m/d/yy Since none of the dates in the sample give a clear indication, the only clue I have is the sequence of the list. It is incorrect if it is m/d/y format but correct for d/m/y
As d/m/y, both dates would still return the same record but it would be - USA GBP 01/08/2016 0.73
I don't understand why your original post indicated that four records would be returned. It should never be more than 2- one for each input date.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect thanks
For anything less that 01/01/2008 the nearest date would be this - for anything current and after 01/08/2016 it would be this date (until a later date is added.)
would be this: What is this?