Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Barbara.

Asked: November 08, 2019 - 10:47 am UTC

Answered by: Chris Saxon - Last updated: November 11, 2019 - 2:46 pm UTC

Category: SQL - Version: 12c

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Median in SQL

You Asked

Dear AskTom-Team!

I wonder whether it is possible to join two tables that have time ranges. E.g a table 'firmname' holds the name of a firm with two columns from_year and to_year that define the years the name is valid. Table 'address' holds the address of a firm with the two columns from_year and to_year defining the years the address is valid. I want to have a table that shows me for each year the valid name and the valid address.


CREATE TABLE firmname (
    id           NUMBER NOT NULL,
    firm_id      NUMBER NOT NULL,
    name         VARCHAR2(150) NOT NULL,
    from_year    SMALLINT NOT NULL,
    to_year      SMALLINT DEFAULT NULL
);

ALTER TABLE firmname ADD CONSTRAINT pk_fnam PRIMARY KEY ( id );


CREATE SEQUENCE fnam_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER fnam_id_trg BEFORE
    INSERT ON firmname
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := fnam_id_seq.nextval;
END;
/

CREATE TABLE address (
    id           NUMBER NOT NULL,
    firm_id      NUMBER NOT NULL,
    street       VARCHAR2(70),
    zipcode      CHAR(5 CHAR) NOT NULL,
    city         VARCHAR2(40) NOT NULL,
    from_year    SMALLINT NOT NULL,
    to_year      SMALLINT DEFAULT NULL
    );


ALTER TABLE address ADD CONSTRAINT pk_addr PRIMARY KEY ( id );


CREATE SEQUENCE addr_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER addr_id_trg BEFORE
    INSERT ON address
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := addr_id_seq.nextval;
END;
/
insert into firmname(firm_id,name,from_year,to_year)values(1,'Müller GmbH',2011,NULL);
insert into firmname(firm_id,name,from_year,to_year)values(2,'Maier AG',2015,2016);
insert into firmname(firm_id,name,from_year,to_year)values(2,'Maier GmbH',2017,NULL);
insert into firmname(firm_id,name,from_year,to_year)values(3,'Mustermann und Söhne',2017,2018);
insert into firmname(firm_id,name,from_year,to_year)values(3,'Mustermann',2019,NULL);


insert into address(firm_id,street,zipcode,city,from_year,to_year)values(1,'Hufestr. 185', '66764', 'Dortmund',2011, 2015);
insert into address(firm_id,street,zipcode,city,from_year,to_year)values(1,'Parkstr. 95', '66794', 'Dortmund',2016, NULL);
insert into address(firm_id,street,zipcode,city,from_year,to_year)values(2,'Feldmark 19', '57223', 'Kreuztal',2015, NULL);
insert into address(firm_id,street,zipcode,city,from_year,to_year)values(3,'Siegener Str. 28', '59368', 'Werne',2017, 2017);
insert into address(firm_id,street,zipcode,city,from_year,to_year)values(3,'Aachener Str. 30', '52062', 'Aachen',2018, NULL);


Thanks :-)

and we said...

So you want to show the active name and address for each company at each time?

Sure you can. But first I'm going to massage the data a bit.

I'm assuming that to_year in the data is INCLUSIVE. So the address for firm_id 3 from/to 2017 means:

The whole of 2017 (1 Jan 00:00 - 31 Dec 23:59).

i.e. all dates up to but not including the 1 Jan 2018.

I'll add one to all the to_years and make them EXCLUSIVE. So the row described above becomes (from,to) (2017,2018) and is active when:

1 Jan 2017 <= sysdate < 1 Jan 2018


As we'll see, this makes the join easier to write. But it also avoids awkward questions about gaps (if somethings starts and ends on 2017, when exactly does it finish? And is a row with a start of 2018 directly after it or not?).

Also I'll set "current" rows (to_year is null) to this year + 1.

I've put these in named subqueries. And joined the results on firm_id.

The start date for each row is now:
greatest ( firms.from_year, addresses.from_year ) 


And the end date:

least ( firms.to_year, addresses.to_year )


Which all together gives:

with firms as (
  select f.from_year from_year, 
         nvl ( f.to_year, extract ( year from sysdate ) ) + 1 to_year,
         f.name, f.id, f.firm_id
  from   firmname f
), addresses as (
  select a.from_year, 
         nvl ( a.to_year, extract ( year from sysdate ) ) + 1 to_year,
         a.street, a.id, a.firm_id
  from   address a
)
select f.firm_id, 
       greatest ( f.from_year, a.from_year ) st,
       least ( f.to_year, a.to_year ) en,
       f.name, a.street
from   firms f
join   addresses a
on     a.firm_id = f.firm_id
order  by f.firm_id, st, en; 

FIRM_ID    ST      EN      NAME                    STREET             
         1    2011    2016 Müller GmbH             Hufestr. 185        
         1    2016    2020 Müller GmbH             Parkstr. 95         
         2    2015    2017 Maier AG                Feldmark 19         
         2    2017    2020 Maier GmbH              Feldmark 19         
         3    2017    2018 Mustermann und Söhne    Siegener Str. 28    
         3    2018    2019 Mustermann und Söhne    Aachener Str. 30    
         3    2019    2018 Mustermann              Siegener Str. 28     -- start after end?!
         3    2019    2020 Mustermann              Aachener Str. 30


As you can see, there's a row with a start date after it's end date.

To exclude it, ensure every row's start is before it's end:

with firms as (
  select f.from_year from_year, 
         nvl ( f.to_year, extract ( year from sysdate ) ) + 1 to_year,
         f.name, f.id, f.firm_id
  from   firmname f
), addresses as (
  select a.from_year, 
         nvl ( a.to_year, extract ( year from sysdate ) ) + 1 to_year,
         a.street, a.id, a.firm_id
  from   address a
)
select f.firm_id, 
       greatest ( f.from_year, a.from_year ) st,
       least ( f.to_year, a.to_year ) en,
       f.name, a.street
from   firms f
join   addresses a
on     a.firm_id = f.firm_id
and    greatest ( f.from_year, a.from_year ) < least ( f.to_year, a.to_year )
order  by f.firm_id, st, en;

FIRM_ID    ST      EN      NAME                    STREET             
         1    2011    2016 Müller GmbH             Hufestr. 185        
         1    2016    2020 Müller GmbH             Parkstr. 95         
         2    2015    2017 Maier AG                Feldmark 19         
         2    2017    2020 Maier GmbH              Feldmark 19         
         3    2017    2018 Mustermann und Söhne    Siegener Str. 28    
         3    2018    2019 Mustermann und Söhne    Aachener Str. 30    
         3    2019    2020 Mustermann              Aachener Str. 30 

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.