quote:

---

Originally posted by sprotson

Each system and subsystem is versioned when any changes to these are made.

The version number is stored in system table and subsystem tables respectively.

The date the change was made is stored in the LatestChanges view whihc can be used to get the date/time a change was made and who made the change amongst other things.

---

SELECT s.SysVersion,ss.SubSysVersion,f.[Date]
FROM dbo.CalendarTable(@StartDate,@EndDate,0,0) f
CROSS APPLY (SELECT MAX(SystemVersion) AS SysVersion
             FROM SystemTable
             WHETE [Date] <= f.[Date]
            )s
CROSS APPLY (SELECT MAX(SubSystemVersion) AS SubSysVersion
             FROM SubSystemTable
             WHETE [Date] <= f.[Date]
            )ss

quote:

---

Originally posted by sprotson

That data refers to line 3 and 4 of the sample data.


Line 3 - 3/1/12. The system has changed again so it is now at version 3. It is still associated with subsystem version 1.
Line 4 - 4/1/12. The sub system has changed to version 2. It is associated with version 3 of the system.

Version 3 of the system is showing in the report twice as this is the ideal scenarion where by a change to the susbsystem and therefore the creation of a new version of the subsystem would return the version of the system it is assigned to.

---

quote:

---

Originally posted by sprotson

Yes, they each have their own table which contains the data for each system and each subsystem.

The two tables are joined in the report by way of a third table "Link", whihc contains the ID of each system and ID of each subsystem.

The version number and date/time of the change is kept in two tables, "Latest Changes System" and "latest Changes Subsystem".

---

SELECT s.Version AS SysVersion,ss.SubSysVersion, s.[date]
FROM System s
CROSS APPLY (SELECT MAX(Version) AS SubSysVersion
             FROM SubSystem ss1
             INNER JOIN Link l
             ON l.SubsystemID = ss1.SubSystemID
             WHERE ss1.[date] <= s.[date]
             AND l.SystemID = s.SystemID
            )ss

UNION

SELECT s.SysVersion, ss.Version, s.[date]
FROM SubSystem ss
CROSS APPLY (SELECT MAX(Version) AS SysVersion
             FROM System s1
             INNER JOIN Link l
             ON l.systemID = s1.SystemID
             WHERE s1.[date] <= ss.[date]
             AND l.SubSystemID = ss.SubSystemID
            )s
ORDER BY [date]