SQL Server – requête pour la plage de dates la plus proche

Si j'ai une structure de table comme ceci:

ProductCode Date Foo 4/1/2012 Foo 4/2/2012 Foo 4/3/2012 Foo 4/6/2012 Foo 4/7/2012 Foo 4/8/2012 Foo 4/9/2012 Foo 4/10/2012 Foo 4/15/2012 Foo 4/16/2012 Foo 4/17/2012 

Existe-t-il un moyen de searchr la plage de dates pour un ProductCode et une Date donnés (en supposant que les plages DOIVENT être séquentielles)? En d'autres termes, pour cette table, Foo existe sur 3 plages de dates: 4/1-4/3 ; 4/6-4/10 ; et 4/15-4/17 et je cherche la plage de dates donnée une date.

S'il vous plaît noter que Foo n'a pas de date 4/4 , 4/5 , 4/11 , 4/12 , 4/13 et 4/14 .

Exemples:
ProductCode=Foo, Date=4/2 4/2 returnnera 4/1-4/3 parce que les inputs sont séquentielles.
ProductCode=Foo, Date=4/4 ne returnnerait rien
ProductCode=Foo, Date=4/7 4/7 returnnera 4/6-4/10 parce que les inputs sont séquentielles.
ProductCode=Foo, Date=4/12 ne returnnerait rien
etc.

Une nouvelle plage commence lorsqu'il n'y a pas de ligne pour le jour précédent. Si vous exécutez SQL Server 2012, vous pouvez utiliser la fonction de window de lag pour vérifier si une ligne introduit une nouvelle plage. Une fois que vous savez quelles lignes introduisent une nouvelle plage, vous pouvez countr le nombre de lignes de tête pour atsortingbuer un nombre unique à chaque plage.

Avoir un numéro de plage vous permet de find la date de début et de fin avec min et max . Après cela, c'est juste une question de sélection de la ligne:

 ; with IsHead as ( select ProductCode , Date , case when lag(Date) over (partition by ProductCode order by Date) = dateadd(day, -1, Date) then 0 else 1 end as IsHead from YourTable ) , RangeNumber as ( select ProductCode , Date , sum(IsHead) over (partition by ProductCode order by Date) as RangeNr from IsHead ) , Ranges as ( select * , min(Date) over (partition by RangeNr) as RangeStart , max(Date) over (partition by RangeNr) as RangeEnd from RangeNumber ) select * from Ranges where ProductCode = 'Bar' and Date = '4/2/2012' 

Exemple à SQL Fiddle.

Pourrait avoir utilisé LAG, si SQL Server 2005 le prenait en charge. Malheureusement , la fonction de window LAG fonctionne uniquement sur SQL Server 2012 et PostgreSQL 8.4 et plus 😉

Fonctionne sur SQL Server 2005 Je suppose, SQLFiddle n'a pas de support SQL 2005, a essayé SQL Server 2008 SQL Server seulement, pas 2012:

 with DetectLeaders as ( select cr.ProductCode, CurRowDate = cr.Date, PrevRowDate = pr.Date from tbl cr left join tbl pr on pr.ProductCode = cr.ProductCode AND cr.Date = DATEADD(DAY,1,pr.Date) ), MembersLeaders as ( select *, MemberLeader = (select top 1 CurRowDate from DetectLeaders nearest where nearest.PrevRowDate is null and nearest.ProductCode = DetectLeaders.ProductCode and DetectLeaders.CurRowDate >= nearest.CurRowDate order by nearest.CurRowDate desc) from DetectLeaders ) select BeginDate = MIN(CurRowDate), EndDate = MAX(CurRowDate) from MembersLeaders where MemberLeader = (select MemberLeader from MembersLeaders where ProductCode = 'Foo' and CurRowDate = '4/7/2012') 

Test en direct: http://sqlfiddle.com/#!3/3fd1f/1


Fondamentalement, voici comment cela fonctionne:

 PRODUCTCODE CURROWDATE PREVROWDATE MEMBERLEADER Foo 2012-04-01 2012-04-01 Foo 2012-04-02 2012-04-01 2012-04-01 Foo 2012-04-03 2012-04-02 2012-04-01 Foo 2012-04-06 2012-04-06 Foo 2012-04-07 2012-04-06 2012-04-06 Foo 2012-04-08 2012-04-07 2012-04-06 Foo 2012-04-09 2012-04-08 2012-04-06 Foo 2012-04-10 2012-04-09 2012-04-06 Foo 2012-04-15 2012-04-15 Foo 2012-04-16 2012-04-15 2012-04-15 Foo 2012-04-17 2012-04-16 2012-04-15 Bar 2012-05-01 2012-05-01 Bar 2012-05-02 2012-05-01 2012-05-01 Bar 2012-05-03 2012-05-02 2012-05-01 Bar 2012-05-06 2012-05-06 Bar 2012-05-07 2012-05-06 2012-05-06 Bar 2012-05-08 2012-05-07 2012-05-06 Bar 2012-05-09 2012-05-08 2012-05-06 Bar 2012-05-10 2012-05-09 2012-05-06 Bar 2012-05-15 2012-05-15 Bar 2012-05-16 2012-05-15 2012-05-15 Bar 2012-05-17 2012-05-16 2012-05-15 

http://sqlfiddle.com/#!3/35818/11

Pourrait faire avec un CTE récursif.

 declare @target_date datetime = convert(datetime, '04/07/2012', 101); with source_table as ( select ProductCode, convert(datetime, Date, 101) as Date from ( values ('Foo', '4/1/2012') ,('Foo', '4/2/2012') ,('Foo', '4/3/2012') ,('Foo', '4/6/2012') ,('Foo', '4/7/2012') ,('Foo', '4/8/2012') ,('Foo', '4/9/2012') ,('Foo', '4/10/2012') ,('Foo', '4/15/2012') ,('Foo', '4/16/2012') ,('Foo', '4/17/2012') ) foo(ProductCode, Date) ), recursive_date_lower as ( select Date from source_table where Date = @target_date union all select dateadd(d, -1, r.Date) from recursive_date_lower r where exists (select 0 from source_table s where s.Date = dateadd(d, -1, r.Date)) ), recursive_date_upper as ( select Date from source_table where Date = @target_date union all select dateadd(d, 1, r.Date) from recursive_date_upper r where exists (select 0 from source_table s where s.Date = dateadd(d, 1, r.Date)) ) select (select min(Date) from recursive_date_lower) as start, (select max(Date) from recursive_date_upper) as finish 

Note: J'ai ajouté une deuxième solution (non récursive) qui a less de lectures logiques (meilleure performance).

1) Vous pourriez utiliser un CTE récursif (démo ici ):

 DECLARE @Test TABLE ( ID INT IDENTITY NOT NULL UNIQUE, --ID is for insert order ProductCode VARCHAR(10) NOT NULL, [Date] SMALLDATETIME NOT NULL, PRIMARY KEY(ProductCode, [Date]) ); INSERT @Test (ProductCode , [Date]) SELECT 'Foo' , '20120401' UNION ALL SELECT 'Foo' , '20120402' UNION ALL SELECT 'Foo' , '20120403' UNION ALL SELECT 'Foo' , '20120404' --UNION ALL SELECT 'Foo' , '20120405' UNION ALL SELECT 'Foo' , '20120406' UNION ALL SELECT 'Foo' , '20120407' UNION ALL SELECT 'Foo' , '20120408' UNION ALL SELECT 'Foo' , '20120409' UNION ALL SELECT 'Foo' , '20120410' UNION ALL SELECT 'Foo' , '20120415' UNION ALL SELECT 'Foo' , '20120416' UNION ALL SELECT 'Foo' , '20120417'; DECLARE @MyProductCode VARCHAR(10), @MyDate SMALLDATETIME; SELECT @MyProductCode = 'Foo', @MyDate = '20120402'; WITH CteRecursive AS ( --Starting row SELECT t.ID, t.ProductCode, t.[Date], 1 AS RowType FROM @Test t WHERE t.ProductCode = @MyProductCode AND t.[Date] = @MyDate UNION ALL --Add the next days DATEADD(DAY, +1, ..) SELECT crt.ID, crt.ProductCode, crt.[Date], 2 AS RowType FROM CteRecursive prev INNER JOIN @Test crt ON DATEADD(DAY, 1, prev.[Date]) = crt.[Date] AND prev.RowType IN (1,2) UNION ALL --Add the previous days DATEADD(DAY, -1, ..) SELECT crt.ID, crt.ProductCode, crt.[Date], 0 AS RowType FROM CteRecursive prev INNER JOIN @Test crt ON DATEADD(DAY, -1, prev.[Date]) = crt.[Date] AND prev.RowType IN (0,1) ) SELECT * FROM CteRecursive r ORDER BY r.[Date] /*--Or SELECT MIN(r.[Date]) AS BeginDate, MAX(r.[Date]) AS EndDate FROM CteRecursive r */ 

Résultats:

 ID ProductCode Date RowType ----------- ----------- ----------------------- ------- 1 Foo 2012-04-01 00:00:00 0 2 Foo 2012-04-02 00:00:00 1 3 Foo 2012-04-03 00:00:00 2 4 Foo 2012-04-04 00:00:00 2 

2) Solution non récursive:

 DECLARE @Test TABLE ( ProductCode VARCHAR(10) NOT NULL, [Date] SMALLDATETIME NOT NULL, PRIMARY KEY(ProductCode, [Date]) ); INSERT @Test (ProductCode , [Date]) SELECT 'Foo' , '20120401' UNION ALL SELECT 'Foo' , '20120402' UNION ALL SELECT 'Foo' , '20120403' UNION ALL SELECT 'Foo' , '20120404' --UNION ALL SELECT 'Foo' , '20120405' UNION ALL SELECT 'Foo' , '20120406' UNION ALL SELECT 'Foo' , '20120407' UNION ALL SELECT 'Foo' , '20120408' UNION ALL SELECT 'Foo' , '20120409' UNION ALL SELECT 'Foo' , '20120410' UNION ALL SELECT 'Foo' , '20120415' UNION ALL SELECT 'Foo' , '20120416' UNION ALL SELECT 'Foo' , '20120417'; DECLARE @MyProductCode VARCHAR(10), @MyDate SMALLDATETIME; SELECT @MyProductCode = 'Foo', @MyDate = '20120402'; DECLARE @StartDate SMALLDATETIME, @EndDate SMALLDATETIME; SELECT @EndDate = MAX(b.[Date]) FROM ( SELECT a.[Date], ROW_NUMBER() OVER(ORDER BY a.Date ASC)-1 AS RowNum FROM @Test a WHERE a.ProductCode = @MyProductCode AND a.[Date] >= @MyDate ) b WHERE b.[Date] = DATEADD(DAY, b.RowNum, @MyDate); SELECT @StartDate = MIN(b.[Date]) FROM ( SELECT a.[Date], ROW_NUMBER() OVER(ORDER BY a.Date DESC)-1 AS RowNum FROM @Test a WHERE a.ProductCode = @MyProductCode AND a.[Date] <= @MyDate ) b WHERE b.[Date] = DATEADD(DAY, -b.RowNum, @MyDate); SELECT @StartDate [@StartDate], @EndDate [@EndDate]; SELECT LEFT(CONVERT(VARCHAR(10), @StartDate, 101),5) [@StartDate], LEFT(CONVERT(VARCHAR(10), @EndDate, 101),5) [@EndDate]; 

Résultats:

 @StartDate @EndDate ----------------------- ----------------------- 2012-04-01 00:00:00 2012-04-04 00:00:00 @StartDate @EndDate ---------- -------- 04/01 04/04 

Vous pouvez essayer quelque chose comme ceci (en supposant SQL Server 2005+):

 WITH partitioned AS ( SELECT ProductCode, Date, GroupID = DATEDIFF(DAY, 0, Date) - ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY Date) FROM atable ), ranges AS ( SELECT ProductCode, Date, MinDate = MIN(Date) OVER (PARTITION BY ProductCode, GroupID), MaxDate = MAX(Date) OVER (PARTITION BY ProductCode, GroupID) FROM partitioned ) SELECT MinDate, MaxDate FROM ranges WHERE ProductCode = @ProductCode AND Date = @Date 

Vous pouvez également utiliser CROSS APPLY pour find la date la plus proche:

 with DetectLeaders as ( select cr.ProductCode, CurRowDate = cr.Date, PrevRowDate = pr.Date from tbl cr left join tbl pr on pr.ProductCode = cr.ProductCode AND cr.Date = DATEADD(DAY,1,pr.Date) ), MembersLeaders as ( select * from DetectLeaders cross apply( select top 1 MemberLeader = CurRowDate from DetectLeaders nearest where nearest.PrevRowDate is null and nearest.ProductCode = DetectLeaders.ProductCode and DetectLeaders.CurRowDate >= nearest.CurRowDate order by nearest.CurRowDate desc ) as xxx ) select BeginDate = MIN(CurRowDate), EndDate = MAX(CurRowDate) from MembersLeaders where MemberLeader = (select MemberLeader from MembersLeaders where ProductCode = 'Foo' and CurRowDate = '4/7/2012') 

Test en direct: http://sqlfiddle.com/#!3/3fd1f/2


Fondamentalement, voici comment cela fonctionne:

 PRODUCTCODE CURROWDATE PREVROWDATE MEMBERLEADER Foo 2012-04-01 2012-04-01 Foo 2012-04-02 2012-04-01 2012-04-01 Foo 2012-04-03 2012-04-02 2012-04-01 Foo 2012-04-06 2012-04-06 Foo 2012-04-07 2012-04-06 2012-04-06 Foo 2012-04-08 2012-04-07 2012-04-06 Foo 2012-04-09 2012-04-08 2012-04-06 Foo 2012-04-10 2012-04-09 2012-04-06 Foo 2012-04-15 2012-04-15 Foo 2012-04-16 2012-04-15 2012-04-15 Foo 2012-04-17 2012-04-16 2012-04-15 Bar 2012-05-01 2012-05-01 Bar 2012-05-02 2012-05-01 2012-05-01 Bar 2012-05-03 2012-05-02 2012-05-01 Bar 2012-05-06 2012-05-06 Bar 2012-05-07 2012-05-06 2012-05-06 Bar 2012-05-08 2012-05-07 2012-05-06 Bar 2012-05-09 2012-05-08 2012-05-06 Bar 2012-05-10 2012-05-09 2012-05-06 Bar 2012-05-15 2012-05-15 Bar 2012-05-16 2012-05-15 2012-05-15 Bar 2012-05-17 2012-05-16 2012-05-15 

http://www.sqlfiddle.com/#!3/3fd1f/3

CROSS APPLY/OUTER APPLY par rapport à JOIN, échelles bien aussi: http://www.ienablemuch.com/2012/04/outer-apply-walkthrough.html