TSQL Astuce 1 : Comment obtenir la liste des jours consécutifs

Rédigé par marmous Aucun commentaire
Classé dans : Informatique Mots clés : TSQL, developpement, SQL Server

Aujourd'hui, une astuce permettant d'extraire des plages de dates contigües d'un planning.

Dans le cas d'un planning, nous avons une table Planning comme suit :

 Create table @Planning (UserId uniqueidentifier, Activities nvarchar(255), Date smalldatetime)

Elle contient ceci :
 

 select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140801'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140802'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140803'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140804'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140805'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140806'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140807'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140808'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140809'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Vacances', '20140810'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Repos', '20140811'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Repos', '20140812'
union
select '12120B41-6C76-41B4-A0A7-5C7F48214865EF', 'Repos', '20140813'

Il faut d'abord faire une première requête en tagant les enregistrements étant consécutif.
Pour détecter les enregistrements consécutifs on utilise ici la commande ROW_NUMBER() OVER (http://msdn.microsoft.com/fr-fr/library/ms186734.aspx) quelque peu détournée de sa fonction première. On obtient donc la même valeur DtRange pour des date qui se suivent.
Nous utilisons ensuite cette première requête en sous requête pour regrouper les valeurs par intervalle de date.

Voici la requête finale :

 select ip2.UserId, 
ip2.Activities,
Min(ip2.Date) as deb,
Max(ip2.Date) as fin
from(select UserId,
Activities,
Date,
DateDiff(Day, Row_Number() over(order by Date), Date) AS DtRange
from @Planning
) as ip2
Group by ip2.DtRange, ip2.UserId, ip2.Activities

Les commentaires sont fermés.