--===========================================================================--
-- 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 7 : "in Memory"                                                  --
--▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀--

USE DB_TEMPORAL;
GO

-- fonction OVERLAPS façon norme SQL:
CREATE OR ALTER FUNCTION dbo.F_OVERLAPS (@D1 sql_variant, @F1 sql_variant,
                                     @D2 sql_variant, @F2 sql_variant)
RETURNS BIT 
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
     RETURN CASE  WHEN @D1 >= @F2 OR @D2 >= @F1 THEN 0 ELSE 1 END
   END;
GO

-- table avec des périodes "valides" (temps opérationnel)
CREATE TABLE dbo.T_EMPLOYE_VALID_EMV
(
	EMV_ID INT NOT NULL DEFAULT (NEXT VALUE FOR SQC_EMP),
	EMV_MATRICULE             CHAR(6) NOT NULL,
	EMV_NOM                   CHAR(32) NOT NULL,
	EMV_PRENOM                VARCHAR(25) NULL,
	EMV_DATE_NAISSANCE        DATE NULL,
	EMV_DATE_EMBAUCHE         DATE NOT NULL,
	EMV_DATE_DEBAUCHE         DATE NULL,
	EMV_SALAIRE_MENSUEL       DECIMAL(16, 2) NOT NULL,
	EMV_HEURES                FLOAT NOT NULL,
	EMV_STATUT                CHAR(12) NOT NULL,
	EMV_FONCTION              VARCHAR(32) NOT NULL,
	EMV_SERVICE               VARCHAR(16) NOT NULL,
	_EMV_DH_DEBUT_OPERATION   DATETIME2(7) DEFAULT SYSUTCDATETIME(),
	_EMV_DH_FIN_OPERATION     DATETIME2(7) DEFAULT '9999-12-31T23:59:59.9999999',
	CONSTRAINT CK_EMV_DEB_FIN CHECK (_EMV_DH_DEBUT_OPERATION <= _EMV_DH_FIN_OPERATION),
   CONSTRAINT PK_EMP PRIMARY KEY CLUSTERED (EMV_ID, _EMV_DH_DEBUT_OPERATION DESC)
GO


-- trigger de contrôle pour non chevauchement
CREATE TRIGGER E_IU_DEB_FIN_OVERLAPS
ON dbo.T_EMPLOYE_VALID_EMV
FOR INSERT, UPDATE
AS
SET NOCOUNT ON;
IF EXISTS(SELECT 1
          FROM   (SELECT EMV_ID _EMV_DH_DEBUT_OPERATION, _EMV_DH_FIN_OPERATION  
                  FROM   dbo.T_EMPLOYE_VALID_EMV
		            WHERE  EMV_ID IN (SELECT EMV_ID 
		                              FROM   inserted)
		            EXCEPT  
		            SELECT EMV_ID _EMV_DH_DEBUT_OPERATION, _EMV_DH_FIN_OPERATION  
                  FROM   inserted) AS E
                 JOIN inserted AS i
                         ON E.EMV_ID = i.EMV_ID
         WHERE   dbo.F_OVERLAPS(E._EMV_DH_DEBUT_OPERATION, E._EMV_DH_FIN_OPERATION,
                                i._EMV_DH_DEBUT_OPERATION, i._EMV_DH_FIN_OPERATION)
BEGIN
   ROLLBACK;
   THROW 66666, 'Pour un même employé, les périodes opérationelles ne doivent pas se chevaucher.', 1;
END;
GO