Try an approach like this.
First identify the rows that have changed by using LAG.
The do a running total on those change points.
Once you know that you can do the Group By but include the groups.
DROP TABLE IF EXISTS #Source
CREATE TABLE #Source (RowNum_Helper INT, ItemID CHAR(1), Classification CHAR(1), Start_Date DATETIME2, End_Date DATETIME2)
To provide an answer to this problem, I did the following (all of the code below is on the fiddle here):
CREATE TABLE test
row_number INTEGER NOT NULL,
code TEXT NOT NULL,
from_date DATE NOT NULL,
to_date DATE NULL
INSERT INTO test VALUES
(1, 'A', '2021-09-07', NULL),
(2, 'A', '2021-04-01', '2021-09-06'),
This is a classic gaps-and-islands situation.
Select longest continuous sequence
Like in the referenced questions, I'm going to use the Tabibitosan method to separate our groups, then just select the first one.
WITH start AS (
Definitive answer based on criteria outlined below. The discussion below refers to minor differences in the criteria for the selection of records (don't include NULLs) and their ordering - I finally used the OP's ordering and not the one discussed in the analysis below this section.
name, MAX(rn2) AS cnt, fv_sd, lv_sd
WITH FIRST1 AS
( SELECT ID, NAME, DATE1, IS_FIRST,
ROW_NUMBER() OVER (ORDER BY ID) AS RN
ID, NAME, DATE1,
CASE WHEN ( LAG(NAME,1) OVER (ORDER BY ID) <> NAME
OR LAG(NAME,1) OVER (ORDER BY ID) IS NULL )
AND LEAD(NAME,1) OVER (ORDER BY ID) = NAME
THEN 1 ELSE ...
This is a classic gaps-and-islands problem.
There are many solutions. A standard method is to define starting or ending points for each section, then use a windowed conditional COUNT to number the islands. Then you simply group by this grouping number.
There are added complications here
We need to check if the previous row is negative but this is not
The table's primary key space is fragmented.
As long as you're not likely to run out (I can't recall if the autoincrement values are limited to 32- or 64-bits), you should not care what the numerical values are.
Auto-increment keys are guaranteed to be unique.
They are not guaranteed to be contiguous or even that they will always increase!)
Given this table (and these gaps):
CREATE TABLE dbo.TableWithGaps(id int IDENTITY(1,1), x char(1));
INSERT dbo.TableWithGaps(x) SELECT TOP (50000) 'x'
FROM sys.all_columns AS c
CROSS APPLY (SELECT TOP (100) * FROM sys.all_objects) AS o;
DELETE dbo.TableWithGaps WHERE id BETWEEN 11 AND 1000; -- 990
DELETE dbo.TableWithGaps WHERE id BETWEEN ...