Requête SQL/Algo - Grouper en fonction de l'ordre des lignes

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:

---

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:

---

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:

---

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

Hello,

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

Code:

---

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

---

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.

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.

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 :king:

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

Bon weekend ;)