JackNBox

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

Is my above post not created properly or is it an overly difficult question?

Hello, I need help writing a query for SQL Server 2008 (SP2). The purpose of the query is to show changes to specific fields being tracked over time using table snapshots taken multiple times a month. The database table looks like this: Source tblProjectSnapshot: [ProjectUniqueID], [SnapshotDate], [SnapshotID], [ProjectName], [Status] 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 Source tblMilestoneSnapshot: [SnapshotDate], [SnapshotID], [MilestoneStartDate] 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 [SnapshotID] is autonumber starting from 1 every time [SnapshotDate] is taken so shared unique key between tables is [SnapshotDate] + [SnapshotID]. Assume thousands of snapshot dates and records are in random order. Desired query output: [ProjectName], [ProjectUniqueID], [SnapshotDateChangeRange], [StatusChange], [MilestoneStartDateChange] Apple, B, "1/1/2020 to 2/1/2020", “Changed from Open to Closed”, Null Carrot, C, "1/1/2020 to 2/1/2020", Null, “Changed from 7/7/2021 to 9/12/2021” What would the MS SQL query be to accomplish the desired query output?