SQL Join Against Date Ranges

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.

SQL join against date ranges

You can use lag to find the rows for which end date of previous row is the start date using datediff, then keep a running total of this difference for each row. Rows having the same running total are in the same group, then for each group you can get max and min of start date and end date respectively to get your desired output.

If you're using sql server:

with u as 
(select StartDate,
EndDate,
case when
coalesce(datediff(day, lag(EndDate) over(order by StartDate, EndDate), StartDate), 0) = 0 then 0
else 1 end as change
from #TEMP),
v as
(select *, sum(change) over(order by StartDate, EndDate rows unbounded preceding) as g
from u)
select min(StartDate) as StartDate, max(EndDate) as EndDate from v group by g 

Fiddle

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

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)

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)
*/

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)

---

Related Topics