Sql query join nearest date

I have a sqlite query that I'm trying to write. I have two tables:

TableA (sales): id sales date

TableB (goals): id goal date

I'm selecting from TableA like this: SELECT id,sales,date FROM TableA

Now to the "tricky" part. I need to join TableB to the query because I need the goal field in TableB for each row in TableA. TableB only contains goals for some dates, while TableA contains all dates. So I can't just use TableA.date = TableB.date

Instead, for each row in TableA I need to take the goal from TableB on the date nearest in the past to the date in TableA. Hope I was able to explain what I needed. Can't figure out how to do it..

This question and answers originated from www.stackoverflow.com
Question by (7/20/2010 2:53:06 PM)

Answer

 
SELECT a.id, a.sales, a.date, (SELECT TOP 1 Goal 
                               FROM TableB b WHERE b.date < a.date
                               ORDER BY b.date DESC) As Goal
FROM TableA a

Going off the nearest date in the past.

Answer by
Find More Answers
Related Topics  sql  query  date  join
Related Questions

  • SQL Join on Nearest less than date

    Normally I would just do this in the code itself, but I am curious if this can be accomplished efficiently in TSQL. Table 1 Date - Value Table 2 Date - Discount Table 1 contains entries …

  • SQL Query to show nearest date?

    I'm trying to figure out how to write a MySQL query that will return the closest 3 events in terms of date. This is my table: EVENT_ID EVENT_NAME EVENT_START_DATE(DATETIME) 1 test …

  • SQL query self join

    I am working on a query for a report in Oracle 10g. I need to generate a short list of each course along with the number of times they were offered in the past year (including ones that weren't a…

  • join SQL query problem

    I have following query which returns the product and the lowest sell price found with the quantity of that sell price. Everything works perfectly until I want to get a product that does not have any…

  • SQL Query JOIN Performance

    I wanted to know what is better performance-wise: to put the conditions in the JOIN? (x JOIN y on x.hi = y.hi AND ....) or maybe to put it under the WHERE, and leave the JOIN only with the crossing …

  • Self Join SQL Query

    a few days ago I asked for a solution to a SQL query i needed to work out, my data looks like: meta_id post_id meta_key meta_value 269 4 _apais USA 270 4 _aciudad New york 2…

  • SQL join against date ranges?

    Consider two tables: Transactions , with amounts in a foreign currency: Date Amount ========= ======= 1/2/2009 1500 2/4/2009 2300 3/15/2009 300 4/17/2009 2200 etc. ExchangeRates …

  • Get the nearest/lowest Date SQL

    I have four dates with a different prices for each date: 10/01/2011 $25 10/08/2011 $50 11/17/2011 $100 12/23/2011 $150 SQL: SELECT price FROM MyTable WHERE MyDate <= '10/12/2011' PR…

  • sql query date time

    i have a datestamp field in my table structure. using sql, i want to find any user who registered in yesterdays date using these time range. eg: 2010-02-06 14:00:00 2010-02-07 10:00:00 i will…

  • SQL query Help with OUTER JOIN?

    I have two tables like this. Table1 Column | Type | ---------+------------------+ cod | text | value99 | double precision | Table2 Column | Type | ---------+----…