I want to join many tables with several date interval and reorder them using greatest and least I have sql code that help me to join this tables but dont give all interval .one of them is missing; here
to create tables
CREATE TABLE tauxcotisation ( date_debut date NOT NULL, date_fin date NOT NULL, tauxsal decimal(6,3) NOT NULL, tauxemp decimal(6,3) NOT NULL );
INSERT INTO tauxcotisation ( date_debut , date_fin , tauxsal , tauxemp ) VALUES ('1961-01-01', '1966-06-30', 0.34, 0.34), ('1966-07-01', '1972-06-30', 0.15, 0.34), ('1972-07-01', '1972-09-30', 0.17, 0.34), ('1972-10-01', '1973-06-30', 0.17, 0.34), ('1973-07-01', '1980-02-29', 0.17, 0.34), ('1980-03-01', '1989-06-30', 0.17, 0.34), ('1989-07-01', '1995-12-31', 0.17, 0.34), ('1996-01-01', '2001-12-31', 0.41, 0.34), ('2002-01-01', '2010-01-01', 0.17, 0.34), ('2010-01-02', '2050-01-01', 0.17, 0.34);
CREATE TABLE carriere ( matricule varchar(20) NOT NULL, employeur varchar(20) NOT NULL, corps varchar(20) NOT NULL, grade varchar(20) NOT NULL, start_sal date NOT NULL, end_sal date NOT NULL, sal_base decimal(9,3) NOT NULL );
INSERT INTO carriere ( matricule , employeur , corps , grade , start_sal , end_sal , sal_base ) VALUES ('994320', 'DBGT','Enseignant', 'Hierarchie A','1992-09-21', '2005-08-12', 4500), ('994320', 'CAMRAIL','Fonctionnaire', 'Hierarchie B','2005-08-13', '2009-09-16', 4900), ('994320', 'CCM','Universitaire', 'Autre','2009-09-17', '2019-07-12', 5300), ('994321', 'DBPNT','Enseignant', 'Hierarchie A','2002-09-21', '2005-08-12', 4500), ('994321', 'CAMRAIL','Fonctionnaire', 'Hierarchie B','2005-08-13', '2009-09-16', 4900), ('994322', 'DOUALPORT','Universitaire', 'Autre','2009-09-17', '2019-07-12', 5300);
CREATE TABLE augmentation ( St_date date NOT NULL, Ed_date date NOT NULL, grade varchar(20) NOT NULL, Montant decimal(9,3) NOT NULL );
INSERT INTO augmentation ( St_date , Ed_date , grade , Montant ) VALUES ('1991-01-21', '2002-09-20','Hierarchie A', 201), ('1991-01-21', '2002-09-20','Hierarchie A', 201), ('2002-09-21', '2006-07-11','Hierarchie A', 200), ('2006-07-12', '2019-09-22','Hierarchie A', 210), ('1991-01-21', '2002-09-20','Hierarchie B', 201), ('1991-01-21', '2002-09-20','Hierarchie B', 201), ('2002-09-21', '2006-07-11','Hierarchie B', 200), ('2006-07-12', '2019-09-22','Hierarchie B', 210), ('1991-01-21', '2002-09-20','Autre', 201), ('1991-01-21', '2002-09-20','Autre', 201), ('2002-09-21', '2006-07-11','Autre', 200), ('2006-07-12', '2019-09-22','Autre', 210)
;
CREATE TABLE rubrique ( deb date NOT NULL, fin date NOT NULL, lblrubrique varchar(20) NOT NULL, corps varchar(20) NOT NULL, tauxrubrique decimal(6,3) NOT NULL );
INSERT INTO rubrique ( deb , fin , lblrubrique , corps , tauxrubrique ) VALUES ('1991-01-01','3002/01/01' ,'css', 'Fonctionnaire', 0.20 ), ('2002/01/01','3002/01/01' , 'ir', 'Fonctionnaire', 0.14 ), ('1991/01/01','3002/01/01' ,'css', 'Enseignant', 0.20 ), ('2002/01/01','3002/01/01' , 'ir', 'Enseignant', 0.14 ), ('1900/06/30', '1989/12/31', 'ie', 'Enseignant', 0.20 ), ('1990/01/01', '2003/09/30', 'ie', 'Enseignant', 0.30), ('2003/10/01', '2005/09/30', 'ie', 'Enseignant', 0.35 ), ('2005/10/01', '2006/09/30', 'ie', 'Enseignant', 0.38), ('2006/10/01', '2007/09/30', 'ie', 'Enseignant', 0.41 ), ('2007/10/01', '2008/09/30', 'ie', 'Enseignant', 0.44 ), ('2008/10/01', '2009/09/30', 'ie', 'Enseignant', 0.47 ), ('2009/10/01', '3009/10/01', 'ie', 'Enseignant', 0.50 ), ('2002/01/01','3002/01/01' ,'css', 'Universitaire', 0.20 ), ('2002/01/01','3002/01/01' , 'ir', 'Universitaire', 0.14 ), ('1900/06/30', '1989/12/31', 'ie', 'Universitaire', 0.20 ), ('1990/01/01', '2003/09/30', 'ie', 'Universitaire', 0.30 ), ('2003/10/01', '2005/09/30', 'ie', 'Universitaire', 0.35 ), ('2005/10/01', '2006/09/30', 'ie', 'Universitaire', 0.38 ), ('2006/10/01', '2007/09/30', 'ie', 'Universitaire', 0.41 ), ('2007/10/01', '2008/09/30', 'ie', 'Universitaire', 0.44 ), ('2008/10/01', '2009/09/30', 'ie', 'Universitaire', 0.47 ), ('2009/10/01', '3009/10/01', 'ie', 'Universitaire', 0.50 ), ('2005/01/01', '2020-10-09', 'irf', 'Universitaire', 1.05);
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
the sql code to join
SELECT greatest(begin_date1,deb) AS begin_date2, least(end_date1,fin) AS end_date2, sal_base, tauxsal, tauxemp, Montant, lblrubrique, corps FROM rubrique,
((SELECT greatest(begin_date,St_date) AS begin_date1, least(end_date,Ed_date) AS end_date1, sal_base, tauxsal, tauxemp, grade, Montant FROM augmentation, ( SELECT greatest(start_sal,date_debut) AS begin_date, least(end_sal,date_fin) AS end_date, sal_base, tauxsal, tauxemp FROM tauxcotisation, carriere WHERE ((start_sal >= date_debut AND start_sal <= date_fin ) OR (end_sal >= date_debut AND end_sal <= date_fin )) AND matricule = '994320' AND (start_sal = '1992-09-21' AND end_sal = '2005-08-12')
)as tab WHERE ( begin_date >= St_date AND begin_date <= Ed_date) OR ( end_date >= St_date AND end_date <= Ed_date))as tab1)
WHERE (( begin_date1 >= deb AND begin_date1 <= fin) OR ( end_date1 >= deb AND end_date1 <= fin)) AND corps = 'Enseignant' AND grade = 'Hierarchie A' ; ++++++++++++++++++++++++++++++++++++++++++++++++++++++
my problem is that the date interval between 1996-01-01 et 2001-12-31 is missing please help me
Thanks