Discussion :

[Oishiiii]

Bonsoir tout le monde,

Je sèche sur une requête SQL.... vous allez certainement pouvoir m'aider.

J'ai une table qui contient des personnes et pour chaque personne son Job title. Et j'ai une ligne par année.

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
 
Person	JobTitle	dateFrom	dateTo
Dupond	Electricien	2001-01-01	2001-12-31
Dupond	Electricien	2002-01-01	2002-12-31
Dupond	Electricien	2003-01-01	2003-12-31
Dupond	Plombier	2004-01-01	2004-12-31
Dupond	Plombier	2005-01-01	2005-12-31
Dupond	Plombier	2006-01-01	2006-12-31
Dupond	Maçon		2007-01-01	2007-12-31
Dupond	Maçon		2008-01-01	2008-12-31
Dupond	Electricien	2009-01-01	2009-12-31
Dupond	Electricien	2010-01-01	2010-12-31
Dupond	Electricien	2011-01-01	NULL

Je souhaite regrouper les lignes, par personnes, par jobs successifs, avec les dates de débuts et de fin qui correspondent.
Pour obtenir ceci :

Code :

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

1
2
3
4
5
6
 
Person	JobTitle	dateFrom	dateTo
Dupond	Electricien	2001-01-01	2003-12-31
Dupond	Plombier	2004-01-01	2006-12-31
Dupond	Maçon		2007-01-01	2008-12-31
Dupond	Electricien	2009-01-01	NULL

La difficulté ici, est de faire 2 groupes, 2 lignes pour "Electricien".
Il faut se baser sur l'ordre de la colonne dateFrom et le Job title pour regrouper.

J'ai essayé de faire une colonne artificielle en jouant avec LEAD(), LAG() et RANK() dans tous les sens, avec CTE récursive etc.. Je n'arrive à rien.

Si vous avez une idée je suis preneur, l'idée étant de ne pas le faire en procédural

Le petit jeu d'essai pour SQL Server :

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
 
;with cte AS (
	select *
	from (
		values	('Dupond', 'Electricien', '2001-01-01', '2001-12-31')
			  , ('Dupond', 'Electricien', '2002-01-01', '2002-12-31')
			  , ('Dupond', 'Electricien', '2003-01-01', '2003-12-31')
			  , ('Dupond', 'Plombier'	, '2004-01-01', '2004-12-31')
			  , ('Dupond', 'Plombier'	, '2005-01-01', '2005-12-31')
			  , ('Dupond', 'Plombier'	, '2006-01-01', '2006-12-31')
			  , ('Dupond', 'Maçon'		, '2007-01-01', '2007-12-31')
			  , ('Dupond', 'Maçon'		, '2008-01-01', '2008-12-31')
			  , ('Dupond', 'Electricien', '2009-01-01', '2009-12-31')
			  , ('Dupond', 'Electricien', '2010-01-01', '2010-12-31')
			  , ('Dupond', 'Electricien', '2011-01-01', NULL)
	) AS td(Person, JobTitle, dateFrom, dateTo)
)
select *
from cte

Merci,
Bonne soirée

[rudib]

Hello,

Voici une idée. Il y a peut-être plus simple.

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
;with cte AS (
	select *
	from (
		values	('Dupond', 'Electricien', '2001-01-01', '2001-12-31')
			  , ('Dupond', 'Electricien', '2002-01-01', '2002-12-31')
			  , ('Dupond', 'Electricien', '2003-01-01', '2003-12-31')
			  , ('Dupond', 'Plombier'	, '2004-01-01', '2004-12-31')
			  , ('Dupond', 'Plombier'	, '2005-01-01', '2005-12-31')
			  , ('Dupond', 'Plombier'	, '2006-01-01', '2006-12-31')
			  , ('Dupond', 'Maçon'		, '2007-01-01', '2007-12-31')
			  , ('Dupond', 'Maçon'		, '2008-01-01', '2008-12-31')
			  , ('Dupond', 'Electricien', '2009-01-01', '2009-12-31')
			  , ('Dupond', 'Electricien', '2010-01-01', '2010-12-31')
			  , ('Dupond', 'Electricien', '2011-01-01', NULL)
	) AS td(Person, JobTitle, dateFrom, dateTo)
),
cte2 as (
	select 
		*,
		coalesce(lag(dateTo) over (partition by Person, JobTitle order by dateFrom), dateadd(day, -1, dateFrom)) as PreviousDateTo,
		row_number() over (partition by Person, JobTitle order by datefrom) as Rn
	from cte
), 
cte3 as (
	SELECT *,
		sum(datediff(day, PreviousDateTo, dateFrom)) over (
			partition by Person, JobTitle 
			order by Rn
			rows between unbounded preceding and current row
		) as GapRn
	FROM cte2
)
SELECT Person, JobTitle, 
	min(DateFrom) as dateFrom, 
	nullif(Max(COALESCE(DateTo, '9999-12-31')), '9999-12-31') as dateTo
FROM cte3
GROUP BY Person, JobTitle, GapRn - Rn
ORDER BY dateFrom

[LapinClair]

Bonjour,

Excellente idée Rudib, en étudiant votre solution ( c'est comme cela que j'essaie de m'améliorer ! ) je me pose une question.
A quoi sert "rows between unbounded preceding and current row" dans votre code ?

Bonne journée.

[rudib]

Il s'agit d'une instruction de cadrage ajoutée à la clause de fenêtrage OVER. Ici, elle permet de créer un total cumulé en indiquant que la somme doit porter sur toutes les lignes précédentes de la fenêtre, jusqu'à la ligne actuelle.

[Oishiiii]

Merci beaucoup Rudi d'avoir pris du temps pour cette requête.

Ta requête répond exactement au besoin, je n'avais pas pensé au Datediff pour trier les lignes, bravo

Je vais continuer à travailler dessus pour voir si j'arrive à le faire d'une autre manière.

Bon weekend