'Get first day of sequence SQL

I would like to get first day of continuous date sequence. Query is valid for one month. I am using SQL Server 2014.

Table example:

Id | VechicleISO | RideISO | CountryISO | StartDate | EndDate
1 | AUS | SLZ | POL | 2022-01-01 | 2022-01-01
2 | AUS | SLZ | POL | 2022-01-02 | 2022-01-02
3 | AUS | SLZ | POL | 2022-01-03 | 2022-01-03
4 | L   | SLZ | POL | 2022-01-28 | 2022-01-28
5 | AUS | SLZ | POL | 2022-01-29 | 2022-01-29
6 | AUS | SLZ | POL | 2022-01-31 | 2022-01-31

Requested output:

Id | VechicleISO | RideISO | CountryISO | StartDate | EndDate | FirstDayOfSequence
1 | AUS | SLZ | POL | 2022-01-01 | 2022-01-01 | 2022-01-01 
2 | AUS | SLZ | POL | 2022-01-02 | 2022-01-02 | 2022-01-01 
3 | AUS | SLZ | POL | 2022-01-03 | 2022-01-03 | 2022-01-01 
4 | L   | SLZ | POL | 2022-01-28 | 2022-01-28 | 2022-01-28 
5 | AUS | SLZ | POL | 2022-01-29 | 2022-01-29 | 2022-01-28
6 | AUS | SLZ | POL | 2022-01-31 | 2022-01-31 | 2022-01-31

I tried to prepare SQL with CTE and INNER JOIN but I have right result only when then sequnece have two rows.

; WITH CTE AS(
    SELECT [Id]
          ,[VechicleISO]
          ,[RideISO]
          ,[CountryISO]
          ,[StartDate]
          ,[EndDate]
          ,ROW_NUMBER() OVER(ORDER BY [StartDate]) AS RowNumber
      FROM [CarReport].[dbo].[TEST2]
)

SELECT 
          [C2].[VechicleISO]
          ,[C2].[RideISO]
          ,[C2].[CountryISO]
          ,[C2].[StartDate]
          ,[C2].[EndDate]
,(CASE WHEN datediff(day, c1.[StartDate], c2.[StartDate]) = 1 THEN CONVERT(VARCHAR, c1.[StartDate], 104) ELSE NULL END  ) as FirstDayOfSequence

FROM CTE c1
RIGHT JOIN CTE C2 ON c1.RowNumber = c2.RowNumber-1

My wrong result:

VechicleISO | RideISO | CountryISO | StartDate | EndDate | FirstDayOfSequence
AUS | SLZ | POL | 2022-01-01 | 2022-01-01 | NULL
AUS | SLZ | POL | 2022-01-02 | 2022-01-02 | 01.01.2022
AUS | SLZ | POL | 2022-01-03 | 2022-01-03 | 02.01.2022
L | SLZ | POL | 2022-01-28 | 2022-01-28 | NULL
AUS | SLZ | POL | 2022-01-29 | 2022-01-29 | 28.01.2022
AUS | SLZ | POL | 2022-01-31 | 2022-01-31 | NULL

Can somebody help me with this topic?

Many thanks



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source