JackNBox
-
Posts
3 -
Joined
-
Last visited
Content Type
Profiles
Forums
Events
Posts posted by JackNBox
-
-
Query Help
in SQL
Is my above post not created properly or is it an overly difficult question?
-
Query Help
in SQL
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?
Query Help
in SQL
Posted
Received working answer from "Will Kong" TechNet: