title: SQL Join Challenge
body: <p>Ok, so I've been stuck on this for 2 days! I've solved it from a semantic point of view but the query can take up to 10 minutes to execute. My database of choice for this is SQLite (for reasons I do not want to elaborate here), but I have tried running the same thing on a SQL Server 2012, it didn't make much of a difference in performance.</p>
  
  <p>So, the problem is that I have 2 tables</p>
  
  <ul>
  <li><code>prices</code> (product_id INT, for_date DATE, value INT)</li>
  <li><code>events</code> (starts_on DATE, ends_on DATE NULLABLE)</li>
  </ul>
  
  <p>I have approximately 500K rows in the prices table and around 100 rows in the events table.</p>
  
  <p>Now I need to write a query to do the following.</p>
  
  <p>Pseudo code is:</p>
  
  <ul>
  <li>For each event: 
  
  <ul>
  <li>IF the event has an ends_on value THEN fetch all product_id(s) that have a matching for_date, For products that DO NOT MATCH then fetch the last for_date which is less than the ends_on value but greater than starts_on for that event.</li>
  <li>ELSE IF the ends_on date of the event is NULL, THEN fetch all product_id(s) that have a for_date that matches to starts_on, For products that DO NOT MATCH fetch the last for_date which is less than the starts_on value.</li>
  </ul></li>
  </ul>
  
  <p>The query I have written in SQL Server 2012 is</p>
  
  <pre><code>SELECT    
      sp.for_date, sp.value
  FROM 
      prices sp
  INNER JOIN 
      events ev ON (((ev.ends_on IS NOT NULL AND
                     (sp.for_date = (SELECT for_date
                                     FROM prices
                                     WHERE for_date &lt;= ev.ends_on 
                                        AND for_date &gt; ev.starts_on
                                     ORDER BY for_date DESC
                                        OFFSET 0 ROWS
                                        FETCH NEXT 1 ROWS ONLY))))
            OR
            ((ev.ends_on is null 
            and
               (sp.for_date = (SELECT for_date
                                  FROM prices
                                  WHERE 
                                      for_date &lt;= ev.starts_on_j
                                      AND for_date &gt; dateadd(day, -14,   ev.starts_on)
                                  order by for_date desc
                                  offset 0 rows
                                      fetch next 1 row only))))
  
                                      );
  </code></pre>
  
  <p>Btw I have also tried to create temp tables with partial data and done the same op on them. It just gets stuck.</p>
  
  <p>The strange thing is if I run the 2 "OR" conditions separately, the response time is perfect !</p>
  
  <p><strong>Update</strong></p>
  
  <p>Sample Dataset and Expected Result</p>
  
  <p><strong>Price Entries</strong></p>
  
  <pre><code>Product ID, ForDt, Value
  1, 25-01-2010, 123
  1, 26-01-2010, 112
  1, 29-01-2010, 334
  1, 02-02-2010, 512
  1, 03-02-2010, 765
  1, 04-02-2010, 632
  1, 05-02-2010, 311
  1, 06-02-2010, 555
  2, 03-02-2010, 854
  2, 04-02-2010, 625
  2, 05-02-2010, 919
  3, 20-01-2010, 777
  3, 06-02-2010, 877
  3, 10-03-2010, 444
  3, 11-03-2010, 888
  </code></pre>
  
  <p><strong>Event Entries</strong> (To make it more understandable, Im adding an event id also)</p>
  
  <pre><code>Event ID, StartsOn, EndsOn
  22, 27-01-2010, NULL
  33, 02-02-2010, 06-02-2010
  44, 01-03-2010, 13-03-2010
  </code></pre>
  
  <p><strong>Expected Result Set</strong></p>
  
  <pre><code>Event ID, Product ID, ForDt, Value
  22, 1, 26-01-2010, 112
  33, 1, 06-02-2010, 311
  44, 1, 06-02-2010, 311
  
  33, 2, 05-02-2010, 919
  44, 2, 05-02-2010, 919
  
  22, 3, 20-01-2010, 777
  33, 3, 06-02-2010, 877
  44, 3, 11-03-2010, 888
  </code></pre>