#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    11
    Rep Power
    0

    Thumbs up Query to take a date from one table and retrieve the nearest date from another table.


    I have two tables which are:

    Transactions
    ------------

    Code:
    date       | Value 
    -------------------------
    01/01/2010 | 14.95        
    02/01/2010 | 15.95        
    05/01/2010 | 15.95        
    10/01/2010 | 14.95
    I then have another table

    ExchangeRates
    ---------------
    Code:
    date        | Rate
    --------------------------
    03/01/2010  | 0.609563
    06/01/2010  | 0.610535
    10/01/2010  | 0.650213
    I want the query to select all the values in the transaction table and find the nearest higher date in the exchange table and give me the rate. So my results would be:

    Query Results
    -------------
    Code:
    date       | value | xdate       |  Rate
    --------------------------------------------
    01/01/2010 | 14.95 | 03/01/2010  | 0.609563  
    02/01/2010 | 15.95 | 03/01/2010  | 0.609563  
    05/01/2010 | 15.95 | 06/01/2010  | 0.610535 
    10/01/2010 | 14.95 | 10/01/2010  | 0.650213
    I think it is probably pretty easy to do but I just cannot get my head around this one. I appreciate the help
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Code:
    SELECT t.date
         , t.value
         , e.date as xdate
         , e.rate
      FROM transactions AS t
    INNER
      JOIN exchangerates AS e
        ON e.date =
           ( SELECT MIN(date)
               FROM exchangerates
              WHERE date >= t.date )
    you actually said "greater than" but by inspecting your desired results, it's clear you meant "equal or greater"

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    11
    Rep Power
    0
    Yes I did mean greater than or equal to. I should of made that clearer.

    I am getting a syntax error on

    Code:
        ON e.date =
           ( SELECT MIN(date)
               FROM exchangerates
              WHERE date >= t.date )
    Can't seem to fix the error. Any ideas?

    Thanks for your help.


    Originally Posted by r937
    Code:
    SELECT t.date
         , t.value
         , e.date as xdate
         , e.rate
      FROM transactions AS t
    INNER
      JOIN exchangerates AS e
        ON e.date =
           ( SELECT MIN(date)
               FROM exchangerates
              WHERE date >= t.date )
    you actually said "greater than" but by inspecting your desired results, it's clear you meant "equal or greater"

  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    my new year's resolution: be more polite

    please, won't you tell me the error message?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    11
    Rep Power
    0
    Syntax error in query expression 'e.date = ( SELECT MIN(date) FROM exchangerates WHERE date >= t.date'.

    that is the error message I am getting.

    I should of also been more clear this is a Ms Access database, I am not sure if that makes any difference.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by YoungL
    ... this is a Ms Access database, I am not sure if that makes any difference.
    it often does

    change this --

    e.date = ( SELECT MIN(date) FROM exchangerates WHERE date >= t.date )

    to this --

    e.date = ( SELECT MIN(date) FROM exchangerates WHERE exchangerates.date >= t.date )
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    11
    Rep Power
    0
    No luck with that. Still getting the same error as above. Sorry for being a pain lol.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by YoungL
    No luck with that. Still getting the same error as above. Sorry for being a pain lol.
    it's not you that's the pain, it's flippin msaccess

    try writing [date] instead of date
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    11
    Rep Power
    0
    It's still not liking it. It just doesn't seem to be liking having the subquery as part of the ON clause.

    I can get the correct date from the second table using this query:

    Code:
    SELECT t.date
         , t.gross
         , ( SELECT MIN(exchangerates.date) AS xdate
             FROM exchangerates
             WHERE t.date <= exchangerates.date
            ) as xdate
    FROM transactions AS t
    But the trouble with this, is I don't know how to also get the rate. Every way I have tried doesn't seem to be working.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    try this --
    Code:
    SELECT y.date
         , y.gross
         , y.xdate
         , z.rate
      FROM ( SELECT t.date
                  , t.gross
                  , MIN(x.date) AS xdate
               FROM transactions AS t
             INNER
               JOIN exchangerates AS x
                 ON x.date >= t.date
             GROUP
                 BY t.date
                  , t.gross ) AS y    
    INNER
      JOIN exchangerates AS z               
        ON z.date = y.xdate
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    11
    Rep Power
    0
    That is awesome, works a treat!!

    Thank you so much for that, really appreciate it!

IMN logo majestic logo threadwatch logo seochat tools logo