Received working answer from "Will Kong" TechNet:
create table tblProjectSnapshot
(
[ProjectUniqueID] varchar(30),
[SnapshotDate] date,
[SnapshotID] int,
[ProjectName] varchar(64),
[Status] varchar(64)
)
insert into tblProjectSnapshot values
('A','1/1/2020',1,'Orange','Open'),
('B','1/1/2020',2,'Apple','Open'),
('C','1/1/2020',3,'Carrot','In Progress'),
('A','2/1/2020',1,'Orange','Open'),
('B','2/1/2020',2,'Apple','Closed'),
('C','2/1/2020',3,'Carrot','In Progress')
GO
create table tblMilestoneSnapshot
(
[SnapshotDate] date,
[SnapshotID] int,
[ProjectName] varchar(64),
[MilestoneStartDate] date
)
insert into tblMilestoneSnapshot values
('1/1/2020', 1,'Orange','5/5/2021'),
('1/1/2020', 2,'Apple','6/6/2021'),
('1/1/2020', 3,'Carrot','7/7/2021'),
('2/1/2020', 1,'Orange','5/5/2021'),
('2/1/2020', 2,'Apple','6/6/2021'),
('2/1/2020', 3,'Carrot','9/12/2021')
GO
select * from tblProjectSnapshot
select * from tblMilestoneSnapshot
--Query
;WITH CTE AS
(
SELECT
T.[ProjectUniqueID],
T.SnapshotDate,
T.SnapshotID,
T.[ProjectName],
T.[Status],
T1.MilestoneStartDate,
ROW_NUMBER() OVER (PARTITION BY T.[ProjectUniqueID] ORDER BY T.SnapshotDate) AS RN
FROM tblProjectSnapshot T
INNER JOIN tblMilestoneSnapshot T1 ON T.SnapshotDate=T1.SnapshotDate
AND T.SnapshotID=T1.SnapshotID
AND T.ProjectName=T1.ProjectName
)
SELECT
S.ProjectUniqueID,S.SnapshotDate,S.ProjectName,CASE WHEN S.[Status]!=S1.[Status] THEN 'Changed from '+S1.[Status]+' to '+S.[Status] ELSE NULL END AS [StatusChange],
CASE WHEN S.MilestoneStartDate!=S1.MilestoneStartDate THEN 'Changed from '+convert(varchar(10),S1.MilestoneStartDate,101)+' to '+ convert(varchar(10),S.MilestoneStartDate,101) END AS [MilestoneStartDateChange]
FROM CTE S
INNER JOIN CTE S1 ON S.ProjectUniqueID=S1.ProjectUniqueID AND S.RN=S1.RN+1
WHERE S.[Status]!=S1.[Status]
OR S.MilestoneStartDate!=S1.MilestoneStartDate
