How to collapse consecutive periods of dates that have the same value [Number] in one in T-SQL?

%3CLINGO-SUB%20id%3D%22lingo-sub-2889507%22%20slang%3D%22en-US%22%3EHow%20to%20collapse%20consecutive%20periods%20of%20dates%20that%20have%20the%20same%20value%20%5BNumber%5D%20in%20one%20in%20T-SQL%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2889507%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%3C%2FP%3E%3CP%3EI%20have%20this%20data%3A%3C%2FP%3E%3CP%3EUSE%20%5BTEST%5D%3CBR%20%2F%3EGO%3CBR%20%2F%3ESET%20ANSI_NULLS%20ON%3CBR%20%2F%3EGO%3CBR%20%2F%3ESET%20QUOTED_IDENTIFIER%20ON%3CBR%20%2F%3EGO%3CBR%20%2F%3ECREATE%20TABLE%20%5Bdbo%5D.%5BMyData%5D(%3CBR%20%2F%3E%5BRES_UID%5D%20%5Buniqueidentifier%5D%20NULL%2C%3CBR%20%2F%3E%5BNumber%5D%20%5Bnumeric%5D(5%2C%202)%20NULL%2C%3CBR%20%2F%3E%5BStartDate%5D%20%5Bdate%5D%20NULL%2C%3CBR%20%2F%3E%5BEndDate%5D%20%5Bdate%5D%20NULL%2C%3CBR%20%2F%3E%5BNum%5D%20%5Bbigint%5D%20NULL%3CBR%20%2F%3E)%20ON%20%5BPRIMARY%5D%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000000'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2014-09-12'%20AS%20Date)%2C%20CAST(N'2017-02-28'%20AS%20Date)%2C%201)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000000'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2017-03-01'%20AS%20Date)%2C%20CAST(N'2017-10-31'%20AS%20Date)%2C%202)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000000'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2017-11-01'%20AS%20Date)%2C%20CAST(N'2018-10-31'%20AS%20Date)%2C%203)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000000'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2018-11-01'%20AS%20Date)%2C%20CAST(N'2020-08-31'%20AS%20Date)%2C%204)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000000'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2020-09-01'%20AS%20Date)%2C%20CAST(N'2021-09-30'%20AS%20Date)%2C%205)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000000'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2021-10-01'%20AS%20Date)%2C%20CAST(N'2049-12-31'%20AS%20Date)%2C%206)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000001'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2019-10-14'%20AS%20Date)%2C%20CAST(N'2021-09-30'%20AS%20Date)%2C%201)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000001'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2021-10-01'%20AS%20Date)%2C%20CAST(N'2049-12-31'%20AS%20Date)%2C%202)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000002'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2016-03-15'%20AS%20Date)%2C%20CAST(N'2018-10-31'%20AS%20Date)%2C%201)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000002'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2018-11-01'%20AS%20Date)%2C%20CAST(N'2020-01-19'%20AS%20Date)%2C%202)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000002'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2020-01-20'%20AS%20Date)%2C%20CAST(N'2020-03-01'%20AS%20Date)%2C%203)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000002'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2020-03-02'%20AS%20Date)%2C%20CAST(N'2020-08-02'%20AS%20Date)%2C%204)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000002'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2020-08-03'%20AS%20Date)%2C%20CAST(N'2049-12-31'%20AS%20Date)%2C%205)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000003'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2017-09-20'%20AS%20Date)%2C%20CAST(N'2019-07-31'%20AS%20Date)%2C%201)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000003'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2019-08-01'%20AS%20Date)%2C%20CAST(N'2020-08-02'%20AS%20Date)%2C%202)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000003'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2020-08-03'%20AS%20Date)%2C%20CAST(N'2021-06-30'%20AS%20Date)%2C%203)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000003'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2021-07-01'%20AS%20Date)%2C%20CAST(N'2021-08-20'%20AS%20Date)%2C%204)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000003'%2C%20CAST(0.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2021-08-21'%20AS%20Date)%2C%20CAST(N'2049-12-31'%20AS%20Date)%2C%205)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000004'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2019-11-11'%20AS%20Date)%2C%20CAST(N'2020-07-01'%20AS%20Date)%2C%201)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000004'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2020-07-02'%20AS%20Date)%2C%20CAST(N'2020-08-02'%20AS%20Date)%2C%202)%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D%2C%20%5BNum%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000004'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2020-08-03'%20AS%20Date)%2C%20CAST(N'2049-12-31'%20AS%20Date)%2C%203)%3CBR%20%2F%3EGO%3C%2FP%3E%3CP%3EHow%20to%20collapse%20consecutive%20periods%20of%20dates%20that%20have%20the%20same%20value%20%5BNumber%5D%20in%20one%20in%20T-SQL%3F%3C%2FP%3E%3CP%3EThe%20result%20table%20is%3A%3C%2FP%3E%3CP%3EUSE%20%5BTEST%5D%3CBR%20%2F%3EGO%3CBR%20%2F%3ESET%20ANSI_NULLS%20ON%3CBR%20%2F%3EGO%3CBR%20%2F%3ESET%20QUOTED_IDENTIFIER%20ON%3CBR%20%2F%3EGO%3CBR%20%2F%3ECREATE%20TABLE%20%5Bdbo%5D.%5BMyData2%5D(%3CBR%20%2F%3E%5BRES_UID%5D%20%5Buniqueidentifier%5D%20NULL%2C%3CBR%20%2F%3E%5BNumber%5D%20%5Bnumeric%5D(5%2C%202)%20NULL%2C%3CBR%20%2F%3E%5BStartDate%5D%20%5Bdate%5D%20NULL%2C%3CBR%20%2F%3E%5BEndDate%5D%20%5Bdate%5D%20NULL%3CBR%20%2F%3E)%20ON%20%5BPRIMARY%5D%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData2%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000000'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2014-09-12'%20AS%20Date)%2C%20CAST(N'2049-12-31'%20AS%20Date))%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData2%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000001'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2019-10-14'%20AS%20Date)%2C%20CAST(N'2049-12-31'%20AS%20Date))%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData2%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000002'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2016-03-15'%20AS%20Date)%2C%20CAST(N'2049-12-31'%20AS%20Date))%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData2%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000003'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2017-09-20'%20AS%20Date)%2C%20CAST(N'2021-08-20'%20AS%20Date))%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData2%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000003'%2C%20CAST(0.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2021-08-21'%20AS%20Date)%2C%20CAST(N'2049-12-31'%20AS%20Date))%3CBR%20%2F%3EGO%3CBR%20%2F%3EINSERT%20%5Bdbo%5D.%5BMyData2%5D%20(%5BRES_UID%5D%2C%20%5BNumber%5D%2C%20%5BStartDate%5D%2C%20%5BEndDate%5D)%20VALUES%20(N'00000000-0000-0000-0000-000000000004'%2C%20CAST(1.00%20AS%20Numeric(5%2C%202))%2C%20CAST(N'2019-11-11'%20AS%20Date)%2C%20CAST(N'2049-12-31'%20AS%20Date))%3CBR%20%2F%3EGO%3C%2FP%3E%3CP%3EWith%20best%20regards%2C%20Vasily%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2889507%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Et-sql%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Contributor

Hello.

I have this data:

USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyData](
[RES_UID] [uniqueidentifier] NULL,
[Number] [numeric](5, 2) NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL,
[Num] [bigint] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2014-09-12' AS Date), CAST(N'2017-02-28' AS Date), 1)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2017-03-01' AS Date), CAST(N'2017-10-31' AS Date), 2)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2017-11-01' AS Date), CAST(N'2018-10-31' AS Date), 3)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2018-11-01' AS Date), CAST(N'2020-08-31' AS Date), 4)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-09-01' AS Date), CAST(N'2021-09-30' AS Date), 5)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2021-10-01' AS Date), CAST(N'2049-12-31' AS Date), 6)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000001', CAST(1.00 AS Numeric(5, 2)), CAST(N'2019-10-14' AS Date), CAST(N'2021-09-30' AS Date), 1)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000001', CAST(1.00 AS Numeric(5, 2)), CAST(N'2021-10-01' AS Date), CAST(N'2049-12-31' AS Date), 2)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2016-03-15' AS Date), CAST(N'2018-10-31' AS Date), 1)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2018-11-01' AS Date), CAST(N'2020-01-19' AS Date), 2)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-01-20' AS Date), CAST(N'2020-03-01' AS Date), 3)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-03-02' AS Date), CAST(N'2020-08-02' AS Date), 4)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-08-03' AS Date), CAST(N'2049-12-31' AS Date), 5)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(1.00 AS Numeric(5, 2)), CAST(N'2017-09-20' AS Date), CAST(N'2019-07-31' AS Date), 1)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(1.00 AS Numeric(5, 2)), CAST(N'2019-08-01' AS Date), CAST(N'2020-08-02' AS Date), 2)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-08-03' AS Date), CAST(N'2021-06-30' AS Date), 3)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(1.00 AS Numeric(5, 2)), CAST(N'2021-07-01' AS Date), CAST(N'2021-08-20' AS Date), 4)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(0.00 AS Numeric(5, 2)), CAST(N'2021-08-21' AS Date), CAST(N'2049-12-31' AS Date), 5)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000004', CAST(1.00 AS Numeric(5, 2)), CAST(N'2019-11-11' AS Date), CAST(N'2020-07-01' AS Date), 1)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000004', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-07-02' AS Date), CAST(N'2020-08-02' AS Date), 2)
GO
INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000004', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-08-03' AS Date), CAST(N'2049-12-31' AS Date), 3)
GO

How to collapse consecutive periods of dates that have the same value [Number] in one in T-SQL?

The result table is:

USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyData2](
[RES_UID] [uniqueidentifier] NULL,
[Number] [numeric](5, 2) NULL,
[StartDate] [date] NULL,
[EndDate] [date] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2014-09-12' AS Date), CAST(N'2049-12-31' AS Date))
GO
INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000001', CAST(1.00 AS Numeric(5, 2)), CAST(N'2019-10-14' AS Date), CAST(N'2049-12-31' AS Date))
GO
INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2016-03-15' AS Date), CAST(N'2049-12-31' AS Date))
GO
INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(1.00 AS Numeric(5, 2)), CAST(N'2017-09-20' AS Date), CAST(N'2021-08-20' AS Date))
GO
INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(0.00 AS Numeric(5, 2)), CAST(N'2021-08-21' AS Date), CAST(N'2049-12-31' AS Date))
GO
INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000004', CAST(1.00 AS Numeric(5, 2)), CAST(N'2019-11-11' AS Date), CAST(N'2049-12-31' AS Date))
GO

With best regards, Vasily

 

0 Replies