Link to home
Start Free TrialLog in
Avatar of RichardAtk
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
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

please clarify:  
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?
Avatar of RichardAtk
RichardAtk

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.
Avatar of PatHartman
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.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect thanks