E
The question you ask fits perfectly with a problem known in Sql as "finding gap and islands".There are several ways to solve this problem.Create table dbo.FindingIslangds (Fecha date, val bit)
go
Insert into dbo.FindingIslangds (Fecha, val)
values
('20190212',0),
('20190213',0),
('20190214',0),
('20190215',0),
('20190216',1),
('20190217',1),
('20190218',1),
('20190219',0),
('20190220',0),
('20190221',0),
('20190222',0),
('20190223',0),
('20190224',1),
('20190225',1),
('20190226',1),
('20190227',1),
('20190228',1),
('20190301',0),
('20190302',0),
('20190303',0),
('20190304',1),
('20190305',1),
('20190306',0),
('20190307',0),
('20190308',0);
GO
For stage dates are inserted in ISO format (yyyyMMdd).The first thing that requires this solution is to have a Sql Server version 2012 or higher, as the window function is used. https://javifer2.wordpress.com/2018/09/11/lag-y-lead-siguiente-y-anterior/ WITH c
AS (SELECT *,
CASE
WHEN DATEDIFF(day, LAG(Fecha) OVER(PARTITION BY val
ORDER BY Fecha,
val), Fecha) = 1
THEN 0
ELSE 1
END AS grp
FROM dbo.FindingIslangds t),
c2
AS (SELECT *,
SUM(grp) OVER(PARTITION BY val
ORDER BY fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grupo
FROM c),
c3 AS (
SELECT MIN(Fecha) AS fechainicio,
MAX(Fecha) AS fechafin,
val
FROM c2
GROUP BY val,
grupo
)
SELECT Format(c3.fechainicio,'d','es-es') as Inicio,
Format(c3.fechafin,'d','es-es') as Fin
FROM c3
WHERE val = 1;
The operation is as follows:On the whole c We set a column called grp, which tells us if the difference in days over the date of the current row is equal to 1 then we know it is a consecutive value, so we put the flag grp = 0, but it is fulfilled, means there is a jump so we put a grp = 1. In the second set c2 we add, the ones of the flag grp, obtaining groups of changes. We have "packages" of information for each change made. In the third set c3, we extract the minimum and maximum value of dates for each "package". Here we would have been able to finish the query, making a waltz restriction = 1. Finally, we give the format expected to the output.However, since you have not set the definition of your origin of data, and only an image of which you cannot extract much information, if in your origin of data, the columns are not date, you will have to perform a cast (col as date), so that the solution is functional. https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/