SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) 
 Oct  8 2022 05:58:25 
 Copyright (C) 2022 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



if exists(select top 1 1 from sys.tables where name='ObjInfo')
drop table ObjInfo

  create table ObjInfo(id int identity
                     ,ObjNumber int
,ObjDate datetime
,ObjConditionId int)
 
  insert into ObjInfo(ObjNumber,ObjDate,ObjConditionId)
  values(1,'2014-01-03',1)
  ,(1,'2014-01-05',1)
  ,(1,'2014-01-06',1)
  ,(1,'2014-01-08',2)
  ,(1,'2014-01-13',1)
  ,(1,'2014-01-15',1)
  ,(1,'2014-01-25',4)
  ,(2,'2014-01-01',1)
  ,(2,'2014-01-05',1)
  ,(2,'2014-01-07',2)
  ,(2,'2014-01-08',2)
  ,(2,'2014-01-12',2)
  ,(2,'2014-01-14',3)
  ,(2,'2014-01-15',4)
14 rows affected

select ObjNumber, ObjConditionId, min(ObjDate) MinObjDate, max(ObjDate) MaxObjDate
from (
    select o.*,
        row_number() over(partition by ObjNumber                 order by ObjDate) rn1,
        row_number() over(partition by ObjNumber, ObjConditionId order by ObjDate) rn2
    from ObjInfo o
) o
group by ObjNumber, ObjConditionId, rn1 - rn2
order by ObjNumber, MinObjDate


ObjNumber
ObjConditionId
MinObjDate
MaxObjDate


1
1
2014-01-03 00:00:00.000
2014-01-06 00:00:00.000

1
2
2014-01-08 00:00:00.000
2014-01-08 00:00:00.000

1
1
2014-01-13 00:00:00.000
2014-01-15 00:00:00.000

1
4
2014-01-25 00:00:00.000
2014-01-25 00:00:00.000

2
1
2014-01-01 00:00:00.000
2014-01-05 00:00:00.000

2
2
2014-01-07 00:00:00.000
2014-01-12 00:00:00.000

2
3
2014-01-14 00:00:00.000
2014-01-14 00:00:00.000

2
4
2014-01-15 00:00:00.000
2014-01-15 00:00:00.000


select ObjNumber, ObjConditionId, min(ObjDate) MinObjDate, 
    lead(min(ObjDate), 1, getdate()) over(partition by ObjNumber order by min(ObjDate)) MaxObjDate
from (
    select o.*,
        row_number() over(partition by ObjNumber                 order by ObjDate) rn1,
        row_number() over(partition by ObjNumber, ObjConditionId order by ObjDate) rn2
    from ObjInfo o
) o
group by ObjNumber, ObjConditionId, rn1 - rn2
order by ObjNumber, MinObjDate


ObjNumber
ObjConditionId
MinObjDate
MaxObjDate


1
1
2014-01-03 00:00:00.000
2014-01-08 00:00:00.000

1
2
2014-01-08 00:00:00.000
2014-01-13 00:00:00.000

1
1
2014-01-13 00:00:00.000
2014-01-25 00:00:00.000

1
4
2014-01-25 00:00:00.000
2023-06-06 08:46:17.283

2
1
2014-01-01 00:00:00.000
2014-01-07 00:00:00.000

2
2
2014-01-07 00:00:00.000
2014-01-14 00:00:00.000

2
3
2014-01-14 00:00:00.000
2014-01-15 00:00:00.000

2
4
2014-01-15 00:00:00.000
2023-06-06 08:46:17.283

ObjNumber	ObjConditionId	MinObjDate	MaxObjDate
1	1	2014-01-03 00:00:00.000	2014-01-06 00:00:00.000
1	2	2014-01-08 00:00:00.000	2014-01-08 00:00:00.000
1	1	2014-01-13 00:00:00.000	2014-01-15 00:00:00.000
1	4	2014-01-25 00:00:00.000	2014-01-25 00:00:00.000
2	1	2014-01-01 00:00:00.000	2014-01-05 00:00:00.000
2	2	2014-01-07 00:00:00.000	2014-01-12 00:00:00.000
2	3	2014-01-14 00:00:00.000	2014-01-14 00:00:00.000
2	4	2014-01-15 00:00:00.000	2014-01-15 00:00:00.000