À la suite d’une demande du forum, je me suis penché sur le calcul de durée en jour avec des mois comptables de 30 jours. Comme d’habitude il faut passer par une table de date !
La demande originale est postée ici :
www.developpez.net/forums/d1508019/bases-donnees/ms-sql-server/developpement/
Nous sommes partit de la table suivante :
CREATE TABLE T_DAT
(DAT_DATE DATE PRIMARY KEY,
DAT_AN AS CAST(YEAR(DAT_DATE) AS SMALLINT) PERSISTED,
DAT_MOIS AS CAST(MONTH(DAT_DATE) AS TINYINT) PERSISTED,
DAT_JOUR AS CAST(DAY(DAT_DATE) AS TINYINT) PERSISTED,
DAT_NB_JOUR_FIN_MOIS TINYINT ,
DAT_JOUR_FIN_MOIS TINYINT
);
(DAT_DATE DATE PRIMARY KEY,
DAT_AN AS CAST(YEAR(DAT_DATE) AS SMALLINT) PERSISTED,
DAT_MOIS AS CAST(MONTH(DAT_DATE) AS TINYINT) PERSISTED,
DAT_JOUR AS CAST(DAY(DAT_DATE) AS TINYINT) PERSISTED,
DAT_NB_JOUR_FIN_MOIS TINYINT ,
DAT_JOUR_FIN_MOIS TINYINT
);
Que nous avons rempli avec les 3 requêtes suivantes :
SET NOCOUNT ON;
DECLARE @D DATE
SET @D = '2000-01-01'
WHILE @D < '2100-12-31'
BEGIN
INSERT INTO T_DAT (DAT_DATE) VALUES (@D);
SET @D = DATEADD(DAY, 1, @D)
END;
GO
WITH T0 AS
(
SELECT DAT_DATE, ROW_NUMBER() OVER(PARTITION BY DAT_AN, DAT_MOIS ORDER BY DAT_JOUR DESC) - 1 AS NB_JOUR_FIN_MOIS
FROM T_DAT
)
UPDATE T
SET DAT_NB_JOUR_FIN_MOIS = NB_JOUR_FIN_MOIS
FROM T_DAT AS T
INNER JOIN T0
ON T.DAT_DATE = T0.DAT_DATE;
GO
UPDATE T
SET DAT_JOUR_FIN_MOIS = T2.DAT_JOUR
FROM T_DAT AS T
INNER JOIN T_DAT AS T2
ON T.DAT_AN = T2.DAT_AN
AND T.DAT_MOIS = T2.DAT_MOIS
AND T2.DAT_NB_JOUR_FIN_MOIS = 0
GO
DECLARE @D DATE
SET @D = '2000-01-01'
WHILE @D < '2100-12-31'
BEGIN
INSERT INTO T_DAT (DAT_DATE) VALUES (@D);
SET @D = DATEADD(DAY, 1, @D)
END;
GO
WITH T0 AS
(
SELECT DAT_DATE, ROW_NUMBER() OVER(PARTITION BY DAT_AN, DAT_MOIS ORDER BY DAT_JOUR DESC) - 1 AS NB_JOUR_FIN_MOIS
FROM T_DAT
)
UPDATE T
SET DAT_NB_JOUR_FIN_MOIS = NB_JOUR_FIN_MOIS
FROM T_DAT AS T
INNER JOIN T0
ON T.DAT_DATE = T0.DAT_DATE;
GO
UPDATE T
SET DAT_JOUR_FIN_MOIS = T2.DAT_JOUR
FROM T_DAT AS T
INNER JOIN T_DAT AS T2
ON T.DAT_AN = T2.DAT_AN
AND T.DAT_MOIS = T2.DAT_MOIS
AND T2.DAT_NB_JOUR_FIN_MOIS = 0
GO
Nous avons ensuite créé la fonction de calcul :
CREATE FUNCTION F_MOIS30 (@D1 DATE, @D2 DATE)
RETURNS INT
AS
BEGIN
DECLARE @AN INT, @MOIS INT, @JFM INT
SELECT @AN = DAT_AN, @MOIS = DAT_MOIS, @JFM = DAT_NB_JOUR_FIN_MOIS
FROM T_DAT
WHERE DAT_DATE = @D1;
SELECT @AN = DAT_AN - @AN, @MOIS = DAT_MOIS - @MOIS, @JFM = @JFM + DAT_JOUR
FROM T_DAT
WHERE DAT_DATE = @D2;
RETURN (@AN * 12 + @MOIS - 1) * 30 + @JFM
END;
GO
RETURNS INT
AS
BEGIN
DECLARE @AN INT, @MOIS INT, @JFM INT
SELECT @AN = DAT_AN, @MOIS = DAT_MOIS, @JFM = DAT_NB_JOUR_FIN_MOIS
FROM T_DAT
WHERE DAT_DATE = @D1;
SELECT @AN = DAT_AN - @AN, @MOIS = DAT_MOIS - @MOIS, @JFM = @JFM + DAT_JOUR
FROM T_DAT
WHERE DAT_DATE = @D2;
RETURN (@AN * 12 + @MOIS - 1) * 30 + @JFM
END;
GO
Il ne restait plus qu'Ã la tester :
SELECT dbo.F_MOIS30('2014-03-20', '2015-02-01')
Ce qui donne 312 résultat escompté !
CQFD
Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
Expert S.G.B.D relationnelles et langage S.Q.L
Moste Valuable Professionnal Microsoft SQL Server
Société SQLspot : modélisation, conseil, formation,
optimisation, audit, tuning, administration SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR
