USE test ;
BEGIN TRANSACTION ;
DROP TABLE IF EXISTS PFC ;
DROP TABLE IF EXISTS SOUS_CATEGORIE ;
DROP TABLE IF EXISTS CATEGORIE ;
DROP TABLE IF EXISTS PUISSANCE ;
CREATE TABLE PUISSANCE
(
puissanceId INT IDENTITY,
puissanceCode VARCHAR(8) NOT NULL,
borneInf INT NOT NULL,
borneSup INT NOT NULL,
unite VARCHAR(12) NOT NULL,
CONSTRAINT PUISSANCE_PK PRIMARY KEY(puissanceId),
CONSTRAINT PUISSANCE_AK UNIQUE(puissanceCode),
CONSTRAINT PUISSANCE_UK UNIQUE(borneInf, unite)
);
SET IDENTITY_INSERT PUISSANCE OFF ;
DBCC CHECKIDENT (PUISSANCE, RESEED, 1)
INSERT INTO PUISSANCE (puissanceCode, borneInf, borneSup, unite)
VALUES
('C1', 0, 2, 'CV')
, ('C2', 3, 6, 'CV')
, ('C3', 7, 10, 'CV')
, ('C4', 11, 14, 'CV')
, ('C5', 15, 23, 'CV')
, ('C6', 24, 9999, 'CV')
, ('T1', 0, 1, 'tonne')
, ('T2', 2, 5, 'tonne')
, ('T3', 6, 10, 'tonne')
, ('T4', 11, 15, 'tonne')
, ('T5', 16, 25, 'tonne')
, ('T6', 26, 9999, 'tonne')
, ('R1', 0, 0, 'remorque')
;
SELECT '' as PUISSANCE, * FROM PUISSANCE ;
CREATE TABLE CATEGORIE
(
catId INT IDENTITY,
catCode VARCHAR(4) NOT NULL,
catLibelle VARCHAR(64) NOT NULL,
CONSTRAINT CATEGORIE_PK PRIMARY KEY(catId),
CONSTRAINT CATEGORIE_AK UNIQUE(catCode)
);
SET IDENTITY_INSERT CATEGORIE OFF ;
DBCC CHECKIDENT (CATEGORIE, RESEED, 1) ;
INSERT INTO CATEGORIE (catCode, catLibelle)
VALUES
('1', 'Catégorie 1 : affaires et promenades (AP)')
, ('2', 'Catégorie 2 : transport pour le compte de l''assuré (TPC)')
, ('3', 'Catégorie 3 : transport public de marchandises (TPM)')
, ('4', 'Catégorie 4 : taxis / transport public)')
;
SELECT '' as CATEGORIE, catCode, catLibelle
FROM CATEGORIE ;
CREATE TABLE SOUS_CATEGORIE
(
catId INT,
sousCatId INT IDENTITY,
sousCatCode VARCHAR(4) NOT NULL,
securiteRoutiere INT NOT NULL,
coutPolice INT NOT NULL,
sousCatLibelle VARCHAR(96) NOT NULL,
CONSTRAINT SOUS_CATEGORIE_PK PRIMARY KEY(catId, sousCatId),
CONSTRAINT SOUS_CATEGORIE_AK UNIQUE(sousCatCode),
CONSTRAINT SOUS_CATEGORIE_CATEGORIE_FK FOREIGN KEY(catId)
REFERENCES CATEGORIE(catId)
);
SET IDENTITY_INSERT SOUS_CATEGORIE OFF ;
DBCC CHECKIDENT (SOUS_CATEGORIE, RESEED, 1) ;
INSERT INTO SOUS_CATEGORIE (catId, sousCatCode, securiteRoutiere, coutPolice, sousCatLibelle)
VALUES
((SELECT catId FROM CATEGORIE WHERE catCode = '1'), '1-1', 17500, 10000, ' /')
, ((SELECT catId FROM CATEGORIE WHERE catCode = '2'), '2-1', 10500, 10000, ' /')
, ((SELECT catId FROM CATEGORIE WHERE catCode = '3'), '3-1', 10500, 10000, 'marchandises (tonnage)')
, ((SELECT catId FROM CATEGORIE WHERE catCode = '3'), '3-2', 00000, 00000, 'remorque')
, ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-1', 00000, 10000, 'taxis 4 places')
, ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-2', 00000, 10000, 'taxis 5 places')
, ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-3', 00000, 10000, 'taxis 6 places')
, ((SELECT catId FROM CATEGORIE WHERE catCode = '4'), '4-5', 00000, 10000, 'TPV 18 places')
;
SELECT '' as SOUS_CATEGORIE, catCode, securiteRoutiere, coutPolice, sousCatCode, sousCatLibelle
FROM CATEGORIE as x JOIN SOUS_CATEGORIE as y ON x.catId = y.catId
;
CREATE TABLE PFC
(
puissanceId INT,
catId INT,
sousCatId INT,
respCivile INT NOT NULL,
defenseRecours INT NOT NULL,
CONSTRAINT PFC_PK PRIMARY KEY(puissanceId, catId, sousCatId),
CONSTRAINT PFC_SOUS_CATEGORIE_FK FOREIGN KEY(catId, sousCatId)
REFERENCES SOUS_CATEGORIE(catId, sousCatId)
);
-- inserts - cas de la catégorie 1
INSERT INTO PFC (puissanceId, catId, sousCatId, respCivile, defenseRecours)
VALUES
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C1')
, (SELECT catid FROM CATEGORIE WHERE catCode = '1')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '1-1')
, 201657, 10183)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
, (SELECT catid FROM CATEGORIE WHERE catCode = '1')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '1-1')
, 244399, 12220)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
, (SELECT catid FROM CATEGORIE WHERE catCode = '1')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '1-1')
, 285114, 14756)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
, (SELECT catid FROM CATEGORIE WHERE catCode = '1')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '1-1')
, 346233, 17312)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
, (SELECT catid FROM CATEGORIE WHERE catCode = '1')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '1-1')
, 407332, 20367)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C6')
, (SELECT catid FROM CATEGORIE WHERE catCode = '1')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '1-1')
, 427698, 21385)
;
-- cas de la catégorie 2
INSERT INTO PFC (puissanceId, catId, sousCatId, respCivile, defenseRecours)
VALUES
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
, (SELECT catid FROM CATEGORIE WHERE catCode = '2')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '2-1')
, 401383, 20069)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
, (SELECT catid FROM CATEGORIE WHERE catCode = '2')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '2-1')
, 563124, 28156)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
, (SELECT catid FROM CATEGORIE WHERE catCode = '2')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '2-1')
, 583830, 29192)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
, (SELECT catid FROM CATEGORIE WHERE catCode = '2')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '2-1')
, 693298, 34665)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C6')
, (SELECT catid FROM CATEGORIE WHERE catCode = '2')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '2-1')
, 716102, 35905)
;
-- cas de la catégorie 3
INSERT INTO PFC (puissanceId, catId, sousCatId, respCivile, defenseRecours)
VALUES
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T1')
, (SELECT catid FROM CATEGORIE WHERE catCode = '3')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '3-1')
, 578610, 28931)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T2')
, (SELECT catid FROM CATEGORIE WHERE catCode = '3')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '3-1')
, 694332, 34717)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T3')
, (SELECT catid FROM CATEGORIE WHERE catCode = '3')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '3-1')
, 921888, 46094)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T4')
, (SELECT catid FROM CATEGORIE WHERE catCode = '3')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '3-1')
, 1383054, 69153)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T5')
, (SELECT catid FROM CATEGORIE WHERE catCode = '3')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '3-1')
, 1843776, 92189)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'T6')
, (SELECT catid FROM CATEGORIE WHERE catCode = '3')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '3-1')
, 2764164, 138208)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'R1')
, (SELECT catid FROM CATEGORIE WHERE catCode = '3')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '3-2')
, 1000000, 0)
;
-- cas de la catégorie 4 (partiel)
INSERT INTO PFC (puissanceId, catId, sousCatId, respCivile, defenseRecours)
VALUES
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
, (SELECT catid FROM CATEGORIE WHERE catCode = '4')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '4-1')
, 690102, 34505)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
, (SELECT catid FROM CATEGORIE WHERE catCode = '4')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '4-1')
, 728486, 36424)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
, (SELECT catid FROM CATEGORIE WHERE catCode = '4')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '4-1')
, 806813, 40341)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
, (SELECT catid FROM CATEGORIE WHERE catCode = '4')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '4-1')
, 846877, 42344)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C2')
, (SELECT catid FROM CATEGORIE WHERE catCode = '4')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '4-2')
, 784239, 42344)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C3')
, (SELECT catid FROM CATEGORIE WHERE catCode = '4')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '4-2')
, 822563, 41128)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C4')
, (SELECT catid FROM CATEGORIE WHERE catCode = '4')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '4-2')
, 900947, 45047)
,
((SELECT puissanceId FROM PUISSANCE WHERE puissanceCode = 'C5')
, (SELECT catid FROM CATEGORIE WHERE catCode = '4')
, (SELECT sousCatid FROM SOUS_CATEGORIE WHERE sousCatCode = '4-2')
, 941015, 47051)
;
--------------------
-- Au résultat
---------------------
SELECT unite, borneInf, borneSup
, LEFT(catLibelle,12) as cat, RIGHT(sousCatLibelle,24) as sousCat
, respCivile, defenseRecours, securiteRoutiere, coutPolice
FROM PFC AS x
JOIN PUISSANCE AS y ON x.puissanceId = y.puissanceId
JOIN CATEGORIE AS z ON x.catId = z.catId
JOIN SOUS_CATEGORIE AS t ON x.catId = t.catId and x.sousCatId = t.sousCatId
ORDER BY cat, sousCat, unite, borneInf
;
ROLLBACK ;
=>