FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE Items (
	Id int NOT NULL,
	Name nvarchar(25) NOT NULL,
	[Type] nvarchar(5) NULL,
	Weight INT NOT NULL
)
INSERT INTO Items (Id, Name, [Type], Weight)
VALUES 
(1, 'Item1', 'T1', 2),
(2, 'Item2', 'T1', 1),
(3, 'Item3', 'T1', 3),
(4, 'Item4', 'T2', 3),
(5, 'Item5', 'T3', 3),
(6, 'Item6', 'T3', 2)

CREATE TABLE StatusHistory (
	Id int NOT NULL,
    ItemId int NOT NULL,
    StatusDate DateTime NOT NULL,
    IsActive bit NOT NULL
)

INSERT INTO StatusHistory
(Id, ItemId, StatusDate, IsActive)
VALUES
(1, 1, '2025-01-01', 1),
(2, 2, '2025-01-15', 1),
(3, 3, '2025-02-01', 1),
(4, 4, '2025-01-01', 1),
(5, 4, '2025-02-01', 0),
(6, 5, '2025-01-01', 1),
(7, 6, '2025-01-15', 1),
(8, 5, '2025-02-01', 0)

;WITH OrderedStatusHistory AS
(
	SElECT SH.Id,
		SH.ItemId,
		I.Name,
		I.Type,
		I.Weight,
		SH.StatusDate,
		SH.IsActive,
		ROW_NUMBER() OVER (PARTITION BY SH.ItemId
						   ORDER BY SH.StatusDate) RowNum
	FROM StatusHistory SH
	INNER JOIN Items I
		ON SH.ItemId = I.Id
),
StatusHistoryIntervals AS 
(
	SELECT SH1.ItemId,
		   SH1.Name,
		   SH1.Type,
		   SH1.Weight,
		   SH1.IsActive,
		   SH1.StatusDate FromDate,
		   SH2.StatusDate ToDate,
		   SH1.RowNum
	FROM OrderedStatusHistory SH1
	LEFT JOIN OrderedStatusHistory SH2
		ON SH1.ItemId = SH2.ItemId
		AND SH1.RowNum = SH2.RowNum-1
)
SELECT * FROM StatusHistoryIntervals

ItemId	Name	Type	Weight	IsActive	FromDate	ToDate	RowNum
1	Item1	T1	2	1	2025-01-01 00:00:00.000	NULL	1
2	Item2	T1	1	1	2025-01-15 00:00:00.000	NULL	1
3	Item3	T1	3	1	2025-02-01 00:00:00.000	NULL	1
4	Item4	T2	3	1	2025-01-01 00:00:00.000	2025-02-01 00:00:00.000	1
4	Item4	T2	3	0	2025-02-01 00:00:00.000	NULL	2
5	Item5	T3	3	1	2025-01-01 00:00:00.000	2025-02-01 00:00:00.000	1
5	Item5	T3	3	0	2025-02-01 00:00:00.000	NULL	2
6	Item6	T3	2	1	2025-01-15 00:00:00.000	NULL	1

(6 rows affected)

(8 rows affected)

(8 rows affected)