'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 |
---|