Join Tables by Date Range

SQL join against date ranges?

You could first do a self-join on the exchange rates which are ordered by date so that you have the start and the end date of each exchange rate, without any overlap or gap in the dates (maybe add that as view to your database - in my case I'm just using a common table expression).

Now joining those "prepared" rates with the transactions is simple and efficient.

Something like:

WITH IndexedExchangeRates AS (           
            SELECT  Row_Number() OVER (ORDER BY Date) ix,
                    Date,
                    Rate 
            FROM    ExchangeRates 
        ),
        RangedExchangeRates AS (             
            SELECT  CASE WHEN IER.ix=1 THEN CAST('1753-01-01' AS datetime) 
                    ELSE IER.Date 
                    END DateFrom,
                    COALESCE(IER2.Date, GETDATE()) DateTo,
                    IER.Rate 
            FROM    IndexedExchangeRates IER 
            LEFT JOIN IndexedExchangeRates IER2 
            ON IER.ix = IER2.ix-1 
        )
SELECT  T.Date,
        T.Amount,
        RER.Rate,
        T.Amount/RER.Rate ConvertedAmount 
FROM    Transactions T 
LEFT JOIN RangedExchangeRates RER 
ON (T.Date > RER.DateFrom) AND (T.Date <= RER.DateTo)

Notes:

• You could replace GETDATE() with a date in the far future, I'm assuming here that no rates for the future are known.

• Rule (B) is implemented by setting the date of the first known exchange rate to the minimal date supported by the SQL Server datetime, which should (by definition if it is the type you're using for the Date column) be the smallest value possible.

Joining two tables by date ranges

If you have access to window functions and CTEs you can simply use LEAD:

WITH cte AS (
    SELECT id, date, LEAD(date, 1) OVER (ORDER BY date) AS end_date
    FROM events
)
SELECT cte.id, reference.date AS reference_date, cte.date AS event_date
FROM cte
INNER JOIN reference ON reference.date >= cte.date
    AND (reference.date < cte.end_date OR cte.end_date IS NULL)

SQl join on date range

SELECT 
    name,
    date(start_date),
    date(end_date),
    d.id as Day_Date
FROM       f_table1 a
Left Join  d_table2 d 
    on a.id = d.id 
   and d.`Day Date` between date(a.start_date) and date(a.end_date)

Joining two dataset based on date range

Use BETWEEN operator.

  proc sql;
    create table want as
    select *
    from have1 t1 inner join have2 t2
        on t1.id = t2.id 
        and t2.date BETWEEN t1.date-5 AND t1.date+5
        ;
    quit;

With example data:

data have1;
infile datalines4 delimiter="|";
input id date :date9.;
format date date9.;
datalines4;
1|15DEC2021
2|12NOV2020
3|10JAN2019
;;;;

data have2;
infile datalines4 delimiter="|";
input id date :date9.;
format date date9.;
datalines4;
1|20DEC2021
2|16NOV2020
3|19JAN2019
;;;;

proc sql;
create table want as
select t1.id
from have1 t1 inner join have2 t2
    on t1.id = t2.id 
    and t2.date BETWEEN t1.date-5 AND t1.date+5
    ;
quit;

Result:
   id 
   1
   2

Join Tables on Date Range in Hive

RTFM - quoting LanguageManual Joins

Hive does not support join conditions that are not equality conditions
as it is very difficult to express such conditions as a map/reduce
job.

You may try to move the BETWEEN filter to a WHERE clause, resulting in a lousy partially-cartesian-join followed by a post-processing cleanup. Yuck. Depending on the actual cardinality of your "skill group" table, it may work fast - or take whole days.

Join 2 tables with filter on date range and number

To find rows that aren't matched in another table, you can use the LEFT JOIN/NULL pattern. When you use a LEFT JOIN, the conditions on the child table are put into the ON clause. Conditions on the parent table are put into the WHERE clause.

SELECT p.*
FROM property_tbl AS p
LEFT JOIN booking_tbl AS b
ON p.id = b.propertyID AND booking_tbl.endDate > '17-10-24' AND booking_tbl.startDate < '17-10-31'
WHERE b.propertyID IS NULL
AND p.max_guest > @num_guest

SQL: How to join two tables by their date ranges

Actually, my variant still does not work correctly. The @MinDate1 and @MinDate2 should be compared by each EmployeeTypeID one by one. There it was compared independently.

Here is correct variant of solving this problem:

SELECT MIN(CASE WHEN t1.ValidFrom > t2.ValidFrom THEN t1.ValidFrom ELSE t2.ValidFrom END) AS MinOverlapDate
            FROM Table1 t1
JOIN Table2 t2 ON t1.EmployeeTypeID = t2.EmployeeTypeID
WHERE t1.WorkItemID = 1 AND t2.EmployeeID = 1
AND (t1.ValidFrom <= t2.ValidTo OR t2.ValidTo IS NULL)
AND (t1.ValidTo >= t2.ValidFrom OR t1.ValidTo IS NULL)

Joining tables based on expanded date range

How to implement this range query?

The key is to filter the data twice in each LEFT JOIN.

• 
  
  1. Filter the part whose ask time precede rate time.
• 
  
  
  
  2. Filter the nearest wanted time by max.

SELECT * FROM orders o LEFT JOIN currency_rates c
ON c.currency_id = o.currency_id AND c.valid_from = (
  SELECT max(valid_from) FROM currency_rates
  WHERE valid_from <= o.received_at
);

/* My result:
   id   | received_at | shipping_cost | currency_id | invoice_address_id | delivery_address_id | currency_id |  rate  | valid_from 
--------+-------------+---------------+-------------+--------------------+---------------------+-------------+--------+------------
 385902 | 2020-01-01  |             0 | CZK         |                  1 |                  11 |             |        | 
 386284 | 2020-01-08  |         44.03 | EUR         |                  8 |                     | EUR         |   25.2 | 2020-01-03
 386282 | 2020-01-06  |         11.43 | GBP         |                  6 |                     | GBP         |   28.4 | 2020-01-03
 386278 | 2020-01-02  |         12.83 | USD         |                  2 |                     | USD         | 19.359 | 2019-12-01
 386281 | 2020-01-05  |         12.83 | USD         |                  5 |                  14 | USD         |  20.34 | 2020-01-03
 386279 | 2020-01-03  |         49.36 | USD         |                  3 |                  12 | USD         |  20.34 | 2020-01-03
 386280 | 2020-01-03  |         12.83 | USD         |                  4 |                  13 | USD         |  20.34 | 2020-01-03
 386283 | 2020-01-07  |         12.83 | USD         |                  7 |                  15 | USD         |  20.34 | 2020-01-03
 386285 | 2020-01-11  |         12.83 | USD         |                  9 |                     | USD         | 21.359 | 2020-01-09
 386286 | 2020-02-12  |         62.55 | USD         |                 10 |                     | USD         | 21.359 | 2020-01-09
(10 rows)
*/

Combining two tables with date ranges into one table

You can try to use JOIN on during date and CASE WHEN judgement the date in SELECT clause.

SELECT T1.Range,
      T1.Provider,
      T1.Status, 
      T2.[Price Band],
      CASE WHEN T1.[Valid From] >= T2.[Valid From] THEN T1.[Valid From]
           ELSE T2.[Valid From] END,
      CASE WHEN T1.[Valid To] <= T2.[Valid To] THEN T1.[Valid To]
           ELSE T2.[Valid To] END
FROM T1 INNER JOIN T2 on 
(
    T1.[Valid From] between T2.[Valid From] and T2.[Valid To] 
OR
    T1.[Valid To] between T2.[Valid From] and T2.[Valid To]
) 
AND 
   T1.Range =T2.Range

sqlfiddle

[Results]:

| Range | Provider |    Status | Price Band |           Valid From |             Valid To |
|-------|----------|-----------|------------|----------------------|----------------------|
|  0113 |       BT | Allocated |     Price1 | 2018-01-01T00:00:00Z | 2018-06-30T23:59:59Z |
|  0113 |       BT | Allocated |     Price2 | 2018-07-01T00:00:00Z | 2018-07-14T23:59:59Z |
|  0113 |      BT2 | Allocated |     Price2 | 2018-07-15T00:00:00Z | 2299-12-31T23:59:59Z |

---

Related Topics