Combining two tables with date ranges into one table

Question

As part of my work with billing telecoms data I scrape data from a variety of sources to build an accurate costing system. Currently one table takes data from the regulatory spreadsheets and another from various pricing tables - these are keyed on the telephone number range, so for example

Table 1  (Range, Provider, Status, Valid From, Valid To)
'0113', 'BT', 'Allocated', '2018-01-01 00:00:00', '2018-07-14 23:59:59'
'0113', 'BT2', 'Allocated', '2018-07-15 00:00:00', '2299-12-31 23:59:59'

Table 2  (Range, Price Band, Valid From, Valid To)
'0113', 'Price1', '2018-01-01 00:00:00', '2018-06-30 23:59:59'
'0113', 'Price2', '2018-07-01 00:00:00', '2299-12-31 23:59:59'

Output Table
'0113', 'BT', 'Allocated', 'Price1', '2018-01-01 00:00:00', '2018-06-30 23:59:59'
'0113', 'BT', 'Allocated', 'Price2', '2018-07-01 00:00:00', '2018-07-14 23:59:59'
'0113', 'BT2', 'Allocated', 'Price2', '2018-07-15 00:00:00', '2299-12-31 23:59:59'

Now, I can do this fine with cursors iterating over the first table and then selecting records from the second table and output a series of rows based on how the date ranges overlap with each other, but is there a simple way of doing this with a SQL statement and some joins?

Answer 1

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 |

Answer 2

How about:

create table table1 (
  range varchar(50),
  provider varchar(50),
  status varchar(50),
  valid_from datetime,
  valid_to datetime
);

insert into table1 (range, provider, status, valid_from, valid_to) 
  values ('0113', 'BT', 'Allocated', '2018-01-01 00:00:00', 
          '2018-07-14 23:59:59');
insert into table1 (range, provider, status, valid_from, valid_to) 
   values ('0113', 'BT2', 'Allocated', '2018-07-15 00:00:00', 
          '2299-12-31 23:59:59');

create table table2 (
  range varchar(50),
  price_band varchar(50),
  valid_from datetime,
  valid_to datetime
);

insert into table2 (range, price_band, valid_from, valid_to) 
  values ('0113', 'Price1', '2018-01-01 00:00:00', '2018-06-30 23:59:59');
insert into table2 (range, price_band, valid_from, valid_to) 
  values ('0113', 'Price2', '2018-07-01 00:00:00', '2299-12-31 23:59:59');

Then, the [short] query:

select
   t1.range, t1.provider, t2.price_band, 
   iif(t1.valid_from > t2.valid_from, t1.valid_from, t2.valid_from) as valid_from,
   iif(t1.valid_to < t2.valid_to, t1.valid_to, t2.valid_to) as valid_to
  from table1 t1, table2 t2
  where t1.valid_from between t2.valid_from and t2.valid_to
     or t1.valid_to between t2.valid_from and t2.valid_to

Result:

range  provider  price_band  valid_from             valid_to
-----  --------  ----------  ---------------------  ---------------------
0113   BT        Price1      2018-01-01 00:00:00.0  2018-06-30 23:59:59.0
0113   BT        Price2      2018-07-01 00:00:00.0  2018-07-14 23:59:59.0
0113   BT2       Price2      2018-07-15 00:00:00.0  2299-12-31 23:59:59.0