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
| DECLARE @AN SMALLINT = 2000;
WITH
T_DATE_HE AS
(SELECT DATEFROMPARTS(@AN, 3, 1) AS JOUR, DATEPART(WEEKDAY, DATEFROMPARTS(@AN, 3, 1)) AS JOUR_NUM
UNION ALL
SELECT DATEADD(day, 1, JOUR), DATEPART(weekday, DATEADD(day, 1, JOUR))
FROM T_DATE_HE
WHERE MONTH(DATEADD(day, 1, JOUR)) = 3
),
T_DATE_EH AS
(SELECT DATEFROMPARTS(@AN, CASE WHEN @AN < 1996 THEN 9 ELSE 10 END, 1) AS JOUR,
DATEPART(weekday, DATEFROMPARTS(@AN, CASE WHEN @AN < 1996 THEN 9 ELSE 10 END, 1)) AS JOUR_NUM
UNION ALL
SELECT DATEADD(day, 1, JOUR), DATEPART(weekday, DATEADD(day, 1, JOUR))
FROM T_DATE_EH
WHERE MONTH(DATEADD(day, 1, JOUR)) = CASE WHEN @AN < 1996 THEN 9 ELSE 10 END
),
T_DATE AS
(
SELECT 'Été-Hiver' AS CHANGEMENT, *, RANK() OVER(PARTITION BY JOUR_NUM ORDER BY JOUR DESC) AS N
FROM T_DATE_EH
UNION ALL
SELECT 'Hiver-Été' AS CHANGEMENT, *, RANK() OVER(PARTITION BY JOUR_NUM ORDER BY JOUR DESC) AS N
FROM T_DATE_HE
)
SELECT CHANGEMENT, JOUR
FROM T_DATE
WHERE JOUR_NUM = 7 AND N = 1
ORDER BY JOUR; |
Partager