INSERT INTO LOCALISATION (LocalisationNote) VALUES ('') ;
INSERT INTO CAMION (LocalisationId, CamionImmat, CamionDateAchat)
SELECT MAX(LocalisationId), 'immat01', '2012-07-01' FROM LOCALISATION ;
INSERT INTO LOCALISATION (LocalisationNote) VALUES ('') ;
INSERT INTO CAMION (LocalisationId, CamionImmat, CamionDateAchat)
SELECT MAX(LocalisationId), 'immat02', '2015-07-05' FROM LOCALISATION ;
INSERT INTO LOCALISATION (LocalisationNote) VALUES ('') ;
INSERT INTO LOCAL (LocalisationId, LocalCode, LocalNom)
SELECT MAX(LocalisationId), 'loc01', 'Entrepôt Tartempion' FROM LOCALISATION ;
INSERT INTO LOCALISATION (LocalisationNote) VALUES ('') ;
INSERT INTO CAMION (LocalisationId, CamionImmat, CamionDateAchat)
SELECT MAX(LocalisationId), 'immat03', '2016-04-01' FROM LOCALISATION ;
INSERT INTO LOCALISATION (LocalisationNote) VALUES ('') ;
INSERT INTO LOCAL (LocalisationId, LocalCode, LocalNom)
SELECT MAX(LocalisationId), 'loc02', 'Vendu' FROM LOCALISATION ;
SELECT * FROM CAMION ;
SELECT * FROM LOCAL ;
---------------------------------------------
-- Types de composants
---------------------------------------------
INSERT INTO COMPOSANT_TYPE (ComposantTypeLibelle, ComposantQteMax) VALUES ('moteur', 1) ; -- un seul moteur par camion
INSERT INTO COMPOSANT_TYPE (ComposantTypeLibelle, ComposantQteMax) VALUES ('transmission', 1) ; -- une seule transmission par camion
INSERT INTO COMPOSANT_TYPE (ComposantTypeLibelle, ComposantQteMax) VALUES ('differentiel', 2) ; -- 2 différentiels au plus par camion
----------------------------------------------
-- Moteurs
----------------------------------------------
INSERT INTO COMPOSANT (ComposantTypeId, ComposantDateAchat, Fabriquant, Modele)
SELECT ComposantTypeId, '2011-01-01', 'Perkins', 'modèle 1'
FROM COMPOSANT_TYPE WHERE ComposantTypeLibelle = 'moteur' ;
INSERT INTO MOTEUR (ComposantId, MoteurNumeroSerie, MaxHorsePower)
SELECT MAX(ComposantId), 's01', 40
FROM COMPOSANT ;
INSERT INTO COMPOSANT (ComposantTypeId, ComposantDateAchat, Fabriquant, Modele)
SELECT ComposantTypeId, '2011-04-01', 'Vendeuvre', 'modèle 17'
FROM COMPOSANT_TYPE WHERE ComposantTypeLibelle = 'moteur' ;
INSERT INTO MOTEUR (ComposantId, MoteurNumeroSerie, MaxHorsePower)
SELECT MAX(ComposantId), 's02', 20
FROM COMPOSANT ;
INSERT INTO COMPOSANT (ComposantTypeId, ComposantDateAchat, Fabriquant, Modele)
SELECT ComposantTypeId, '2012-04-01', 'Renault', 'modèle t'
FROM COMPOSANT_TYPE WHERE ComposantTypeLibelle = 'moteur' ;
INSERT INTO MOTEUR (ComposantId, MoteurNumeroSerie, MaxHorsePower)
SELECT MAX(ComposantId), 's03', 30
FROM COMPOSANT ;
SELECT '' AS Moteur, * FROM MOTEUR_COMPOSANT_V ;
---------------------------------------------------------------------------------
-- Affectations successives d'un moteur à des camions et à des entrepôts
---------------------------------------------------------------------------------
INSERT INTO COMPOSANT_AFFECTATION (ComposantId, ComposantInstallationDate, ComposantDesInstallationDate, LocalisationId)
SELECT ComposantId, '2011-01-02', '2015-07-19'
, (SELECT LocalisationId
FROM LOCAL WHERE LocalCode = 'loc01')
FROM MOTEUR
WHERE MoteurNumeroSerie = 's01'
;
INSERT INTO COMPOSANT_AFFECTATION (ComposantId, ComposantInstallationDate, ComposantDesInstallationDate, LocalisationId)
SELECT ComposantId, '2015-07-20', '2016-12-08'
, (SELECT LocalisationId
FROM CAMION WHERE CamionImmat = 'immat01')
FROM MOTEUR
WHERE MoteurNumeroSerie = 's01'
;
INSERT INTO COMPOSANT_AFFECTATION (ComposantId, ComposantInstallationDate, ComposantDesInstallationDate, LocalisationId)
SELECT ComposantId, '2016-12-09', '2017-04-01'
, (SELECT LocalisationId
FROM CAMION WHERE CamionImmat = 'immat02')
FROM MOTEUR
WHERE MoteurNumeroSerie = 's01'
;
INSERT INTO COMPOSANT_AFFECTATION (ComposantId, ComposantInstallationDate, ComposantDesInstallationDate, LocalisationId)
SELECT ComposantId, '2017-04-02', '2017-12-06'
, (SELECT LocalisationId
FROM CAMION WHERE CamionImmat = 'immat03')
FROM MOTEUR
WHERE MoteurNumeroSerie = 's01'
;
INSERT INTO COMPOSANT_AFFECTATION (ComposantId, ComposantInstallationDate, ComposantDesInstallationDate, LocalisationId)
SELECT ComposantId, '2017-12-07', '9999-12-31'
-- SELECT ComposantId, '2017-04-02', '2017-12-31'
, (SELECT LocalisationId
FROM LOCAL WHERE LocalCode = 'loc02')
FROM MOTEUR
WHERE MoteurNumeroSerie = 's01'
;
INSERT INTO COMPOSANT_AFFECTATION (ComposantId, ComposantInstallationDate, ComposantDesInstallationDate, LocalisationId)
SELECT ComposantId, '2012-07-02', '2013-01-02'
, (SELECT LocalisationId
FROM CAMION WHERE CamionImmat = 'immat01')
FROM MOTEUR
WHERE MoteurNumeroSerie = 's02'
;
-- Insert légal (un entrepôt n'est pas un camion et peut accueillir plusieurs moteurs en même temps)
INSERT INTO COMPOSANT_AFFECTATION (ComposantId, ComposantInstallationDate, ComposantDesInstallationDate, LocalisationId)
SELECT ComposantId, '2017-05-01', '2017-05-02'
, (SELECT LocalisationId
FROM LOCAL WHERE LocalCode = 'loc02')
FROM MOTEUR
WHERE MoteurNumeroSerie = 's02'
;
--------------------------------------------------------
-- Tentatives d'infraction (recouvrement des périodes)
--------------------------------------------------------
INSERT INTO COMPOSANT_AFFECTATION (ComposantId, ComposantInstallationDate, ComposantDesInstallationDate, LocalisationId)
-- SELECT ComposantId, '2015-07-18', '2015-07-19'
-- SELECT ComposantId, '2015-07-18', '9999-12-31'
-- SELECT ComposantId, '2017-12-31', '2017-12-31'
-- SELECT ComposantId, '2017-12-31', '2018-01-01'
SELECT ComposantId, '2018-01-01', '2018-01-02'
, (SELECT LocalisationId
FROM CAMION WHERE CamionImmat = 'immat03')
FROM MOTEUR
WHERE MoteurNumeroSerie = 's01'
;
SELECT * FROM COMPOSANT_AFFECTATION ;
------------------------------------------------------------------
-- Tentatives d'infraction (plus d'un moteur pour un camion)
------------------------------------------------------------------
INSERT INTO COMPOSANT_AFFECTATION (ComposantId, ComposantInstallationDate, ComposantDesInstallationDate, LocalisationId)
SELECT ComposantId, '2015-07-18', '2015-07-20'
, (SELECT LocalisationId
FROM CAMION WHERE CamionImmat = 'immat01')
FROM MOTEUR
WHERE MoteurNumeroSerie = 's02'
;
SELECT * FROM COMPOSANT_AFFECTATION ;
GO
CREATE VIEW MOTEUR_LOCALISATION (Numero_moteur, Achat_moteur, Date_installation, Date_désinstallation, Type_localisation, immat_camion_ou_inventaire, entrepot_ou_date_achat_camion)
AS
SELECT MoteurNumeroSerie, ComposantDateAchat, ComposantInstallationDate, ComposantDesInstallationDate, 'Camion ', CamionImmat, CAST(CamionDateAchat AS VARCHAR(10))
FROM MOTEUR AS x JOIN COMPOSANT AS y ON x.ComposantId = y.ComposantId
JOIN COMPOSANT_AFFECTATION AS z ON y.ComposantId = z.ComposantId
JOIN CAMION AS t ON z.LocalisationId = t.LocalisationId
UNION
SELECT MoteurNumeroSerie, ComposantDateAchat, ComposantInstallationDate, ComposantDesInstallationDate, 'Entrepôt/vendu ', LocalCode, LocalNom
FROM MOTEUR AS x JOIN COMPOSANT AS y ON x.ComposantId = y.ComposantId
JOIN COMPOSANT_AFFECTATION AS z ON y.ComposantId = z.ComposantId
JOIN LOCAL AS t ON z.LocalisationId = t.LocalisationId
;
GO
SELECT * FROM MOTEUR_LOCALISATION
ORDER BY Numero_moteur, Date_installation ;
-- ORDER BY immat_camion_ou_inventaire, Numero_moteur, Date_installation desc ;
=>
Partager