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 :-)
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