FREE AI-Enhanced Online SQL Server Compiler

Untitled*

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

DROP TABLE IF EXISTS [dbo].[AssignedTo]

CREATE TABLE [dbo].[AssignedTo]
(
    [WorkId] [INT] NOT NULL,
    [OwnerId] [INT] NOT NULL,
    [ValidFrom] [DATETIME] NULL
) ON [PRIMARY];

INSERT INTO dbo.AssignedTo(WorkId, OwnerId, ValidFrom) VALUES(1,1, '2020-01-01');
INSERT INTO dbo.AssignedTo(WorkId, OwnerId, ValidFrom) VALUES(1,2, '2020-01-02');
INSERT INTO dbo.AssignedTo(WorkId, OwnerId, ValidFrom) VALUES(1,1, '2020-01-03');
INSERT INTO dbo.AssignedTo(WorkId, OwnerId, ValidFrom) VALUES(1,1, '2020-01-04');
INSERT INTO dbo.AssignedTo(WorkId, OwnerId, ValidFrom) VALUES(2,3, '2020-01-05');
INSERT INTO dbo.AssignedTo(WorkId, OwnerId, ValidFrom) VALUES(3,4, '2020-01-06');

;WITH AssignedToWithPrevOwner AS (
	SELECT WorkId, ValidFrom, OwnerId, LAG(OwnerId) OVER (ORDER BY ValidFrom) AS PrevOwnerID
    FROM dbo.AssignedTo
),
DatesAssigned AS (
	SELECT atwo.WorkId, MAX(atwo.ValidFrom) AS DateAdded
    FROM AssignedToWithPrevOwner AS atwo
    WHERE atwo.PrevOwnerID <> atwo.OwnerId OR atwo.PrevOwnerID IS NULL
    GROUP BY atwo.WorkId
)
SELECT * FROM DatesAssigned;

WorkId	DateAdded
1	2020-01-03 00:00:00.000
2	2020-01-05 00:00:00.000
3	2020-01-06 00:00:00.000

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(3 rows affected)