Jump to content
Sign in to follow this  
JackNBox

Query Help

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...