Requete avec row dépendant du précédent

**jolann69** · 06/11/2019, 23h04

Bonsoir à tous,

J'ai une table avec les données suivantes :

Id Object | Start | End

A | 06/11/2019 09:00 | 06/11/2019 09:15
A |06/11/2019 09:05 | 06/11/2019 09:20
A |06/11/2019 09:10 | 06/11/2019 09:25
A |06/11/2019 09:15 | 06/11/2019 09:30
B | 06/11/2019 09:05 | 06/11/2019 09:20
B |06/11/2019 09:10 | 06/11/2019 09:25
B |06/11/2019 09:15 | 06/11/2019 09:30
B |06/11/2019 09:35 | 06/11/2019 09:50

Et je voudrais une requête qui me retourne:

A | 06/11/2019 09:00 | 06/11/2019 09:15
A |06/11/2019 09:15 | 06/11/2019 09:30
B | 06/11/2019 09:05 | 06/11/2019 09:20
B |06/11/2019 09:35 | 06/11/2019 09:50

Pour chaque Objet, je veux récupérer la plus petite date de début, puis ensuite la plus petite date de début qui est supérieure ou égale à la date de fin de la précédente.
Pour l'instant je le fais avec un curseur, mais c'est très moche et pas performant. Je sais qu'il est possible de le faire avec une requête mais je ne sais pas trop comment m'y prendre.

Merci d'avance pour votre aide.
Thomas

Invité · 06/11/2019, 23h39

regarde les fonctions LEAD ou LAG.

**jolann69** · 06/11/2019, 23h49

J'ai regardé la doc de LEAD et LAG avec la commande OVER, mais je t'avoue que je n'ai pas compris le principe. Car moi je ne dois pas juste parcourir le suivant, mais les N suivants jusqu'a trouvé le prochain dont la date de début est supérieure à la date du fin du précédent choisi.

**SQLpro** · 07/11/2019, 12h23

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT T1.*, T2.DEBUT, T2.FIN 
FROM   UNE_TABLE AS T1
       JOIN UNE_TABLE AS T2 ON  T1.ID_OBJET = T2.ID_OBJET 
                            AND T2.DEBUT >= T1.FIN
                            AND T1.FIN < T2.FIN 
WHERE NOT EXISTS(SELECT *
                 FROM   UNE_TABLE AS T
                 WHERE  T.ID_OBJET = T1.ID_OBJET
                 AND  T.DEBUT < T1.DEBUT
                 AND  T.DEBUT < T1.FIN)

Apprenez le SQL ! Mon livre est fait pour cela :
Nom : SQL.jpg
Affichages : 435
Taille : 47,4 Ko

A +

**jolann69** · 08/11/2019, 10h45

Bonjour,
Merci pour ta réponse. J'ai essayé ce que tu as mis avec un jeu d'essai :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
DECLARE @Planning_Proposal_Temp AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2)
INSERT INTO @Planning_Proposal_Temp(object, dateFrom, dateTo ) 
SELECT * 
  FROM (VALUES 
       ('A', '2019-11-06 09:00:00', '2019-11-06 09:15:00'),
       ('A', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
       ('A', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
       ('A', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
	   ('A', '2019-11-06 09:20:00', '2019-11-06 09:35:00'),
	   ('A', '2019-11-06 09:25:00', '2019-11-06 09:40:00'),
	   ('A', '2019-11-06 09:30:00', '2019-11-06 09:45:00'),
	   ('A', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
	   ('A', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
	   ('A', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
       ('B', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
       ('B', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
       ('B', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
       ('B', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
	   ('B', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
	   ('B', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
	   ('B', '2019-11-06 10:00:00', '2019-11-06 10:15:00'),
	   ('B', '2019-11-06 10:05:00', '2019-11-06 10:20:00'),
	   ('B', '2019-11-06 10:10:00', '2019-11-06 10:25:00'),
	   ('B', '2019-11-06 10:15:00', '2019-11-06 10:30:00'),
	   ('C', '2019-11-06 12:00:00', '2019-11-06 12:20:00'),
	   ('C', '2019-11-06 12:40:00', '2019-11-06 13:00:00'),
	   ('C', '2019-11-06 12:45:00', '2019-11-06 13:05:00'),
	   ('C', '2019-11-06 12:50:00', '2019-11-06 13:10:00'),
	   ('C', '2019-11-06 12:55:00', '2019-11-06 13:15:00'),
	   ('C', '2019-11-06 13:00:00', '2019-11-06 13:20:00'),
	   ('C', '2019-11-06 13:05:00', '2019-11-06 13:25:00'),
	   ('C', '2019-11-06 13:10:00', '2019-11-06 13:30:00'),
	   ('C', '2019-11-06 13:15:00', '2019-11-06 13:35:00'),
	   ('C', '2019-11-06 13:20:00', '2019-11-06 13:40:00')
	   ) T(IdObject, StartDate, EndDate)
 
 
SELECT T1.*, T2.dateFrom, T2.dateTo 
FROM   @Planning_Proposal_Temp AS T1
       JOIN @Planning_Proposal_Temp AS T2 ON  T1.object = T2.object 
                            AND T2.dateFrom >= T1.dateTo
                            AND T1.dateTo < T2.dateTo 
WHERE NOT EXISTS(SELECT *
                 FROM   @Planning_Proposal_Temp AS T
                 WHERE  T.object = T1.object
                 AND  T.dateFrom < T1.dateFrom
                 AND  T.dateFrom < T1.dateTo)

Et ca me retourne le résultat suivant :
Nom : res1.png
Affichages : 364
Taille : 30,2 Ko

Voici le code du Curseur que j'utilise et que je désire remplacer avec le même jeu de données:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
102
103
104
105
106
107
108
109
110
111
112
 
DECLARE @Planning_Proposal_Temp AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2)
INSERT INTO @Planning_Proposal_Temp(object, dateFrom, dateTo ) 
SELECT * 
  FROM (VALUES 
       ('A', '2019-11-06 09:00:00', '2019-11-06 09:15:00'),
       ('A', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
       ('A', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
       ('A', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
	   ('A', '2019-11-06 09:20:00', '2019-11-06 09:35:00'),
	   ('A', '2019-11-06 09:25:00', '2019-11-06 09:40:00'),
	   ('A', '2019-11-06 09:30:00', '2019-11-06 09:45:00'),
	   ('A', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
	   ('A', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
	   ('A', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
       ('B', '2019-11-06 09:05:00', '2019-11-06 09:20:00'),
       ('B', '2019-11-06 09:10:00', '2019-11-06 09:25:00'),
       ('B', '2019-11-06 09:15:00', '2019-11-06 09:30:00'),
       ('B', '2019-11-06 09:35:00', '2019-11-06 09:50:00'),
	   ('B', '2019-11-06 09:40:00', '2019-11-06 09:55:00'),
	   ('B', '2019-11-06 09:45:00', '2019-11-06 10:00:00'),
	   ('B', '2019-11-06 10:00:00', '2019-11-06 10:15:00'),
	   ('B', '2019-11-06 10:05:00', '2019-11-06 10:20:00'),
	   ('B', '2019-11-06 10:10:00', '2019-11-06 10:25:00'),
	   ('B', '2019-11-06 10:15:00', '2019-11-06 10:30:00'),
	   ('C', '2019-11-06 12:00:00', '2019-11-06 12:20:00'),
	   ('C', '2019-11-06 12:40:00', '2019-11-06 13:00:00'),
	   ('C', '2019-11-06 12:45:00', '2019-11-06 13:05:00'),
	   ('C', '2019-11-06 12:50:00', '2019-11-06 13:10:00'),
	   ('C', '2019-11-06 12:55:00', '2019-11-06 13:15:00'),
	   ('C', '2019-11-06 13:00:00', '2019-11-06 13:20:00'),
	   ('C', '2019-11-06 13:05:00', '2019-11-06 13:25:00'),
	   ('C', '2019-11-06 13:10:00', '2019-11-06 13:30:00'),
	   ('C', '2019-11-06 13:15:00', '2019-11-06 13:35:00'),
	   ('C', '2019-11-06 13:20:00', '2019-11-06 13:40:00')
	   ) T(IdObject, StartDate, EndDate)
 
 
	   DECLARE @dateFrom_old datetime;
		DECLARE @dateTo_old datetime;
		DECLARE @object_old varchar(10);
 
 
		DECLARE @dateFrom_new datetime;
		DECLARE @dateTo_new datetime;
		DECLARE @object_new varchar(10);
		DECLARE @Planning_Proposal_Temp_Grouped AS TABLE(object varchar(10), dateFrom datetime2, dateTo datetime2)
 
 
DECLARE insertedDataCursor CURSOR 
			FOR SELECT *
				FROM @Planning_Proposal_Temp
				ORDER BY Object, dateFrom
 
 
			OPEN insertedDataCursor 
 
 
			FETCH NEXT FROM insertedDataCursor INTO @object_new, 
													@dateFrom_new, 
													@dateTo_new
 
 
 
			WHILE @@FETCH_STATUS = 0
			BEGIN
 
				IF	@dateFrom_old IS NOT NULL AND
					@dateTo_old IS NOT NULL AND
					@object_old IS NOT NULL
 
				BEGIN
 
					IF ((@object_old <> @object_new) OR (@dateFrom_new >= @dateTo_old))
					BEGIN
						INSERT INTO @Planning_Proposal_Temp_Grouped VALUES( @object_new,
																			@dateFrom_new, 
																			@dateTo_new
																			)
 
					SET @dateFrom_old = @dateFrom_new;
					SET @dateTo_old = @dateTo_new
					SET @object_old = @object_new
 
 
					END
 
				END
 
				ELSE
				BEGIN
					INSERT INTO @Planning_Proposal_Temp_Grouped VALUES( @object_new,
																		@dateFrom_new, 
																		@dateTo_new 
																		)
 
				SET @dateFrom_old = @dateFrom_new;
				SET @dateTo_old = @dateTo_new
				SET @object_old = @object_new
				END
 
 
 
				FETCH NEXT FROM insertedDataCursor INTO @object_new, @dateFrom_new, @dateTo_new
 
 
			END
 
			CLOSE insertedDataCursor 
			DEALLOCATE insertedDataCursor
 
			SELECT * FROM @Planning_Proposal_Temp_Grouped

Et le résultat que je souhaite :

Nom : res2.png
Affichages : 292
Taille : 8,3 Ko

Merci encore pour votre aide.

**aieeeuuuuu** · 08/11/2019, 11h40

bonjour,

Vous pouvez essayer comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 
WITH Tmp AS (
	SELECT  PPT.object, PPT.DateFrom, PPT.DateTo, PPT2.DateFrom AS FromSuiv, PPT2.DateTo AS ToSuiv, ROW_NUMBER() OVER(PARTITION BY PPT.object ORDER BY PPT.DateFrom) AS RN
	FROM @Planning_Proposal_Temp AS PPT
	LEFT JOIN @Planning_Proposal_Temp AS PPT2
		ON PPT2.object = PPT.object
		AND PPT2.dateFrom>= PPT.dateTo
	LEFT JOIN @Planning_Proposal_Temp AS PPT3
		ON PPT3.object = PPT.object
		AND PPT3.dateFrom >= PPT.dateTo
		AND PPT3.dateFrom < PPT2.dateFrom
	WHERE PPT3.object IS NULL 
) 
, CTE AS (
	SELECT object, DateFrom, DateTo, FromSuiv, ToSuiv
	FROM Tmp
	WHERE RN = 1
 
	UNION ALL 
 
	SELECT tmp.object, tmp.DateFrom, tmp.DateTo, tmp.FromSuiv, tmp.ToSuiv
	FROM CTE
	INNER JOIN Tmp
		ON tmp.object = cte.object
		AND tmp.DateFrom = cte.FromSuiv
		AND tmp.DateTo = cte.ToSuiv
)
SELECT object, DateFrom, DateTo
FROM CTE
ORDER BY object, dateFrom

Requete avec row dépendant du précédent

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager