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
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
| DECLARE @DateDebut1 DATETIME
SET @DateDebut1 = '2023-01-01'
DECLARE @DateFin DATETIME
SET @DateFin = GETDATE()
-- Créer une table temporaire pour stocker les résultats
CREATE TABLE #DatesExecutionTemp (
NomJob VARCHAR(255),
NomPlanification VARCHAR(255),
DateExecution DATETIME,
Frequence VARCHAR(100)
)
-- boucle cursor pour parcourir les planifications du job
DECLARE @NomJob VARCHAR(255)
DECLARE @NomPlanification VARCHAR(255)
DECLARE @DateExecution DATETIME
DECLARE @Frequence VARCHAR(100)
DECLARE @jobId UNIQUEIDENTIFIER
DECLARE curPlanifications CURSOR FOR
SELECT
j.name AS NomJob,
s.name AS NomPlanification,
TRY_CONVERT(DATETIME, CONVERT(VARCHAR(8), s.active_start_date, 112) + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), s.active_start_time), 6), 3, 0, ':'), 6, 0, ':')) AS DateExecution,
CASE
WHEN s.freq_type = 1 THEN 'Une seule fois'
WHEN s.freq_type = 4 THEN 'Quotidienne'
WHEN s.freq_type = 8 THEN 'Hebdomadaire'
WHEN s.freq_type = 16 THEN 'Mensuelle'
WHEN s.freq_type = 32 THEN 'Mensuelle relative'
END AS Frequence
FROM
sysschedules s
JOIN sysjobschedules js ON js.schedule_id = s.schedule_id
LEFT JOIN sysjobs j ON j.job_id = js.job_id
WHERE
TRY_CONVERT(DATETIME, CONVERT(VARCHAR(8), s.active_start_date, 112) + ' ' +
STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), s.active_start_time), 6), 3, 0, ':'), 6, 0, ':')) <= @DateFin
AND s.enabled = 1
ORDER BY j.name ASC, DateExecution DESC
OPEN curPlanifications
FETCH NEXT FROM curPlanifications INTO @NomJob, @NomPlanification, @DateExecution, @Frequence
-- boucle pour générer les dates d'exécution
WHILE @@FETCH_STATUS = 0
BEGIN
-- ajout de la date d'exécution initiale dans la table temporaire
INSERT INTO #DatesExecutionTemp (NomJob, NomPlanification, DateExecution, Frequence)
VALUES (@NomJob, @NomPlanification, @DateExecution, @Frequence)
-- Boucle WHILE pour générer les dates d'exécution suivantes en fonction de la fréquence
WHILE @DateExecution <= @DateFin
BEGIN
-- Calcul de la prochaine date d'exécution en fonction de la fréquence
SET @DateExecution =
CASE
WHEN @Frequence = 'Quotidienne' THEN DATEADD(DAY, 1, @DateExecution) -- Quotidienne : ajout d'un jour
WHEN @Frequence = 'Hebdomadaire' THEN DATEADD(WEEK, 1, @DateExecution) -- Hebdomadaire : ajout d'une semaine
WHEN @Frequence = 'Mensuelle' THEN DATEADD(MONTH, 1, @DateExecution) -- Mensuelle : ajout d'un mois
WHEN @Frequence = 'Mensuelle relative' THEN DATEADD(MONTH, 1, @DateExecution) -- Mensuelle relative : ajout d'un mois
END
-- insertion de la nouvelle date d'exécution dans la table temporaire
INSERT INTO #DatesExecutionTemp (NomJob, NomPlanification, DateExecution, Frequence)
VALUES (@NomJob, @NomPlanification, @DateExecution, @Frequence)
END
FETCH NEXT FROM curPlanifications INTO @NomJob, @NomPlanification, @DateExecution, @Frequence
END
CLOSE curPlanifications
DEALLOCATE curPlanifications
SELECT
dt.NomJob,
dt.NomPlanification,
CASE
WHEN EXISTS (
SELECT 1
FROM sysjobhistory h
JOIN sysjobs j ON h.job_id = j.job_id
JOIN sysjobschedules js ON js.job_id = j.job_id
JOIN sysschedules s ON s.schedule_id = js.schedule_id
WHERE CONVERT(VARCHAR(8), h.run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), h.run_time), 6), 3, 0, ':'), 6, 0, ':')
= CONVERT(VARCHAR(8), s.active_start_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), s.active_start_time), 6), 3, 0, ':'), 6, 0, ':')
AND h.step_id = 0
)
THEN dt.DateExecution
ELSE NULL
END AS DateExecution,
dt.Frequence
FROM #DatesExecutionTemp AS dt
WHERE dt.DateExecution BETWEEN @DateDebut1 AND @DateFin
ORDER BY dt.DateExecution DESC, dt.NomJob ASC
DROP TABLE #DatesExecutionTemp |
