Query Help

[JackNBox]

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?

[JackNBox]

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

[justsomeguy]

I don't know of a way to do that with a query.  I think you need a stored procedure or you get all of the relevant data and then process it with another language on the server.  Maybe there's a way to do it with a basic query, but it would be pretty complex and I'm not sure how you'd go about it.

[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

[Funce]

Heck! Power to you, that's pretty heavy duty. Thanks for sharing it back with us.