--===========================================================================--
-- TABLES temporelles  /   MS Cloud Summit 2017   /  2017-01-09  version 1.0 --
--===========================================================================--
-- Fred Brouard, SQLpro -  http://sqlpro.developpez.com - SQLpro@SQLspot.com --
-- Arian papillon -        http://www.datafly.fr/ -   a.papillon@datafly.com --
--===========================================================================--

--▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄--
-- Exemples 4 : Récupération historique dans une table d'historique          --
--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--


/*
USE master;
DROP DATABASE DB_TEMPORAL;
GO
CREATE DATABASE DB_TEMPORAL
GO
*/


USE DB_TEMPORAL
GO

/*
ALTER TABLE dbo.T_EMPLOYE_EMP
   SET (SYSTEM_VERSIONING = OFF) ;
GO
DROP TABLE dbo.T_EMPLOYE_HISTORIQUE_EMP;
DROP TABLE dbo.T_EMPLOYE_EMP;
DROP SEQUENCE SQC_EMP;
*/


CREATE SEQUENCE SQC_EMP AS INT START WITH 1;
GO

CREATE TABLE dbo.T_EMPLOYE_EMP
(EMP_ID                      INT NOT NULL DEFAULT NEXT VALUE FOR SQC_EMP,
 EMP_MATRICULE               CHAR(6) NOT NULL,
 EMP_NOM                     CHAR(32) NOT NULL,
 EMP_PRENOM                  VARCHAR(25),
 EMP_DATE_NAISSANCE          DATE,
 EMP_DATE_EMBAUCHE           DATE NOT NULL,
 EMP_DATE_DEBAUCHE           DATE,
 EMP_SALAIRE_MENSUEL         DECIMAL(16,2) NOT NULL,
 EMP_HEURES                  FLOAT NOT NULL,
 EMP_STATUT                  CHAR(12) NOT NULL 
                             CHECK (EMP_STATUT 
                                       IN ('Manœuvre', 'OS', 'ETAM', 
                                           'Cadre', 'Cadre sup.', 'Dirigeant')),
 EMP_FONCTION                VARCHAR(32) NOT NULL,
 EMP_SERVICE                 VARCHAR(16) NOT NULL,  
 EMP_DH_DEBUT                DATETIME2 NOT NULL, 
 EMP_DH_FIN                  DATETIME2 NOT NULL DEFAULT '9999-12-31T23:59:59.9999999'
 --CONSTRAINT CK_EMP_DEB_FIN CHECK(EMP_DH_DEBUT < EMP_DH_FIN),
 --CONSTRAINT UK_EMP_MAT_DEB UNIQUE(EMP_MATRICULE, EMP_DH_DEBUT),
 --CONSTRAINT PK_EMP PRIMARY KEY (EMP_ID, EMP_DH_DEBUT)
)
GO

INSERT INTO dbo.T_EMPLOYE_EMP 
       (EMP_MATRICULE, EMP_NOM,    EMP_PRENOM, EMP_DATE_NAISSANCE, EMP_DATE_EMBAUCHE, EMP_SALAIRE_MENSUEL, EMP_HEURES, EMP_STATUT,  EMP_FONCTION, EMP_SERVICE,   EMP_DH_DEBUT)
VALUES ('A0001',       'DUPONT',   'Daniel',   '1955-03-12',       '1975-09-02',      400,                 40,         'Manœuvre', 'Cariste',    'STOCK',        '1975-09-01 11:22:33'),
       ('C0001',       'MARTIN',   'Marcel',   '1952-06-17',       '1977-01-02',      410,                 40,         'OS',       'Chauffeur',  'STOCK',        '1977-01-02 09:53:45'),
       ('E0001',       'ENVIN',    'Élise',    '1958-01-23',       '1979-03-01',      400,                 40,         'ETAM',     'Vendeur',    'VENTE',        '1979-03-10 10:22:44'),
       ('E0002',       'AMBLARD',  'Alain',    '1952-11-16',       '1979-09-10',      450,                 40,         'ETAM',     'Vendeur',    'VENTE',        '1979-09-02 10:11:12'),
       ('E0003',       'BONNARD',  'Basil',    '1956-12-01',       '1979-09-15',      500,                 40,         'ETAM',     'Comptable',  'COMPTABILITÉ', '1979-09-02 10:13:57'),
       ('F0001',       'CARDEAU',  'Claude',   '1960-04-21',       '1980-01-10',      850,                 40,         'Cadre',    'Ingénieur',  'CONCEPTION',   '1980-01-20 17:12:21'),
       ('F0002',       'DIDOT',    'Damien',   '1958-02-11',       '1980-04-01',      600,                 40,         'ETAM',     'Tourneur',   'PRODUCTION',   '1980-04-01 09:02:17'),
       ('F0003',       'FORTIER',  'Fabien',   '1959-09-02',       '1980-04-01',      600,                 40,         'ETAM',     'Fraiseur',   'PRODUCTION',   '1980-04-01 09:18:49'),
       ('F0004',       'GAVOT',    'Gérard',   '1961-09-02',       '1980-09-02',      450,                 40,         'ETAM',     'Vendeur',    'VENTE',        '1980-09-01 15:28:43'),
       ('J0001',       'HOUDOT',   'Henri',    '1952-11-29',       '1984-06-01',      1200,                39,         'Cadre',    'DAF',        'DIRECTION',    '1984-06-01 09:59:52');
GO


BEGIN TRANSACTION;

-- reforme 39 heures  => 1/3/1982

INSERT INTO dbo.T_EMPLOYE_EMP 
      (EMP_ID, EMP_MATRICULE, EMP_NOM,    EMP_PRENOM, EMP_DATE_NAISSANCE, EMP_DATE_EMBAUCHE, EMP_SALAIRE_MENSUEL, EMP_HEURES, EMP_STATUT,  EMP_FONCTION, EMP_SERVICE, EMP_DH_DEBUT, EMP_DH_FIN)
SELECT EMP_ID, EMP_MATRICULE, EMP_NOM,    EMP_PRENOM, EMP_DATE_NAISSANCE, EMP_DATE_EMBAUCHE, EMP_SALAIRE_MENSUEL, EMP_HEURES, EMP_STATUT,  EMP_FONCTION, EMP_SERVICE, EMP_DH_DEBUT, '1982-03-01 09:10:37'
FROM   dbo.T_EMPLOYE_EMP AS E1
WHERE  EMP_DH_DEBUT = (SELECT MAX (EMP_DH_DEBUT) FROM dbo.T_EMPLOYE_EMP AS E2 WHERE E1.EMP_MATRICULE = E2.EMP_MATRICULE)
  AND  EMP_DH_DEBUT < '1982-03-01 09:10:37';

UPDATE dbo.T_EMPLOYE_EMP 
SET    EMP_HEURES = 39, EMP_DH_DEBUT = '1982-03-01 09:10:37'
WHERE  EMP_DH_FIN = '9999-12-31T23:59:59.9999999' AND  EMP_DH_DEBUT < '1982-03-01 09:10:37';

-- réforme des 35 heures => 1/1/2002 (et 10% de salaire en moins !)

INSERT INTO dbo.T_EMPLOYE_EMP 
      (EMP_ID, EMP_MATRICULE, EMP_NOM,    EMP_PRENOM, EMP_DATE_NAISSANCE, EMP_DATE_EMBAUCHE, EMP_SALAIRE_MENSUEL, EMP_HEURES, EMP_STATUT,  EMP_FONCTION, EMP_SERVICE, EMP_DH_DEBUT, EMP_DH_FIN)
SELECT EMP_ID, EMP_MATRICULE, EMP_NOM,    EMP_PRENOM, EMP_DATE_NAISSANCE, EMP_DATE_EMBAUCHE, EMP_SALAIRE_MENSUEL, EMP_HEURES, EMP_STATUT,  EMP_FONCTION, EMP_SERVICE, EMP_DH_DEBUT, '2002-01-02 11:22:33'
FROM   dbo.T_EMPLOYE_EMP AS E1
WHERE  EMP_DH_DEBUT = (SELECT MAX (EMP_DH_DEBUT) FROM dbo.T_EMPLOYE_EMP AS E2 WHERE E1.EMP_MATRICULE = E2.EMP_MATRICULE);

UPDATE dbo.T_EMPLOYE_EMP 
SET    EMP_HEURES = 35, EMP_SALAIRE_MENSUEL = EMP_SALAIRE_MENSUEL * 0.9, EMP_DH_DEBUT = '2002-01-02 11:22:33'
WHERE  EMP_DH_FIN = '9999-12-31T23:59:59.9999999' -- AND EMP_DH_DEBUT = '1982-03-01 09:10:37';

SELECT * FROM dbo.T_EMPLOYE_EMP ORDER BY EMP_ID, EMP_DH_DEBUT;

COMMIT
GO

---------------------------------------------------
-- migration vers la temporalisation automatique --
---------------------------------------------------

--> PHASE 1, migration des données en deux tables

--> Déport de l'historisation vers une table d'historisation
SELECT * INTO dbo.T_EMPLOYE_HISTORIQUE_EMP FROM dbo.T_EMPLOYE_EMP WHERE EMP_DH_FIN <> '9999-12-31T23:59:59.9999999';

--> suppression des données de l'historique
DELETE FROM dbo.T_EMPLOYE_EMP WHERE EMP_DH_FIN <> '9999-12-31T23:59:59.9999999';
GO

--> PHASE 1, modification pour temporalisation automatique

-- ajout d'une contrainte PK obligatoire
ALTER TABLE dbo.T_EMPLOYE_EMP
    ADD CONSTRAINT PK_EMP PRIMARY KEY (EMP_ID);
GO

-- ajout des valeurs par défaut
ALTER TABLE dbo.T_EMPLOYE_EMP
    ADD DEFAULT SYSUTCDATETIME() FOR EMP_DH_DEBUT;
GO
-- > si pas déjà !
ALTER TABLE dbo.T_EMPLOYE_EMP
    ADD DEFAULT CAST('9999-12-31' AS DATETIME2) FOR EMP_DH_FIN;
GO

-- mise en place de la période
ALTER TABLE dbo.T_EMPLOYE_EMP
   ADD PERIOD FOR SYSTEM_TIME (EMP_DH_DEBUT, EMP_DH_FIN);
GO

-- et du versionnement
ALTER TABLE dbo.T_EMPLOYE_EMP
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.T_EMPLOYE_HISTORIQUE_EMP, 
                                DATA_CONSISTENCY_CHECK = ON));
GO

--> si l'on veut renommer les colonnes :
EXEC sp_rename 'dbo.T_EMPLOYE_EMP.EMP_DH_DEBUT', '_EMP_DH_DEBUT', 'COLUMN';
EXEC sp_rename 'dbo.T_EMPLOYE_EMP.EMP_DH_FIN', '_EMP_DH_FIN', 'COLUMN';
--> !!! ceci à renommé les colonnes dans les deux tables !
GO

--> les colonnes ne sont pas hidden ! Pour les y rendre :
ALTER TABLE dbo.T_EMPLOYE_EMP ALTER COLUMN _EMP_DH_DEBUT ADD HIDDEN;
ALTER TABLE dbo.T_EMPLOYE_EMP ALTER COLUMN _EMP_DH_FIN ADD HIDDEN;
GO

--> vérification
SELECT * FROM dbo.T_EMPLOYE_EMP FOR SYSTEM_TIME ALL
ORDER BY EMP_ID, _EMP_DH_DEBUT;
GO

--> Structure finale de la table :
CREATE TABLE dbo.T_EMPLOYE_EMP
(
	EMP_ID int NOT NULL DEFAULT NEXT VALUE FOR SQC_EMP PRIMARY KEY,
	EMP_MATRICULE char(6) NOT NULL,
	EMP_NOM char(32) NOT NULL,
	EMP_PRENOM varchar(25) NULL,
	EMP_DATE_NAISSANCE date NULL,
	EMP_DATE_EMBAUCHE date NOT NULL,
	EMP_DATE_DEBAUCHE date NULL,
	EMP_SALAIRE_MENSUEL decimal(16, 2) NOT NULL,
	EMP_HEURES float NOT NULL,
	EMP_STATUT char(8) NOT NULL
                          CHECK (EMP_STATUT 
                                       IN ('Manœuvre', 'OS', 'ETAM', 
                                           'Cadre', 'Dirigeant')),
	EMP_FONCTION varchar(32) NOT NULL,
	EMP_SERVICE varchar(16) NOT NULL,
	_EMP_DH_DEBUT datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT (sysutcdatetime()),
	_EMP_DH_FIN   datetime2(7) GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL DEFAULT ('9999-12-31T23:59:59.9999999'),
   PERIOD FOR SYSTEM_TIME (_EMP_DH_DEBUT, _EMP_DH_FIN)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.T_EMPLOYE_HISTORIQUE_EMP ));
GO



/*
-- nettoyage
ALTER TABLE dbo.T_EMPLOYE_EMP SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.T_EMPLOYE_HISTORIQUE_EMP;
DROP TABLE dbo.T_EMPLOYE_EMP;
*/