1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| CREATE FUNCTION dbo.Fct_Repartition_Mensuelle(@Id Int,
@date_deb datetime,
@date_fin datetime )
RETURNS TABLE
RETURN
WITH CTE_Repartition_Mensuelle( Id, date_deb, date_fin, nb_jour ) AS
(
SELECT @Id AS Id,
DATEADD(month, DATEDIFF(month, 0, @date_deb ), 0) As date_deb,
DATEADD (month, +1 , DATEADD(month, DATEDIFF(month, 0, @date_deb ), 0) ) - 1 AS date_fin,
CASE
WHEN DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, @date_deb) + 1, 0)) <= @date_fin then
DATEDIFF(day, @date_deb, DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, @date_deb) + 1, 0)) ) + 1
ELSE
DATEDIFF(day, @date_deb, @date_fin ) + 1
END AS nb_jour
UNION ALL
SELECT @Id AS Id,
date_fin +1 AS date_deb,
DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, date_fin + 1) + 1, 0)) As date_fin,
CASE
WHEN @date_fin <= DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, date_fin +1) + 1, 0)) then
DATEDIFF(day, date_fin +1, @date_fin) +1
ELSE
DATEDIFF(day, date_fin +1,
DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0,
DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, date_fin + 1) + 1, 0))
) + 1, 0))
) +1
END AS nb_jour
FROM CTE_Repartition_Mensuelle
WHERE date_Fin < DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, @date_fin) + 1, 0))
)
SELECT Id, date_deb, date_fin, nb_jour FROM CTE_Repartition_Mensuelle
GO |
Partager