SNOMED Documentation Search

 Other Documents
Skip to end of metadata
Go to start of metadata

Current Version - Under Revision

The general method for creating a snapshot 'view' for a specified SnapshotTime is as follows:

  1. Exclude all Component versions with an effectiveTime greater than the SnapshotTime.

Note: In theory the most recent snapshot view step could be omitted. However, a release will often be distributed before its effectiveTime . Therefore, this approach is not recommended as a general approach in a live system.

  1. From each set of Component versions with the same id select the Component version with the highest (most recent) effectiveTime .

The most flexible approach is to apply this method dynamically so that a different snapshot time can be configured as needed to meet new requirements. The following example code illustrates an implementable approach to this.

SELECT `c`.* FROM `sct2_concept` AS `c`

WHERE `c`.`effectiveTime` = (SELECT MAX(`c2`.`effectiveTime`)

FROM `sct2_concept` `c2`

WHERE `c2`.`id` = `c`.`id`

AND `c2`.`effectiveTime` <= `snapshotTime`())

Figure 24. General form of SQL to create a snapshot view

In this sample code `snapshotTime()` is a function that returns the time to be applied to this snapshot. For the most recent snapshot view this can be omitted as shown below:

SELECT `c`.* FROM `sct2_concept` AS `c`

WHERE `c`.`effectiveTime` = (SELECT MAX(`c2`.`effectiveTime`)

FROM `sct2_concept` `c2`

WHERE `c2`.`id` = `c`.`id`)

Figure 25. SQL to create the latest snapshot view

Similar views can be created for each of the Component tables by simply replacing the table name in both the outer and nested queries.

Note: The SQL queries in this and the following section assume applicability of a common versioning view for all modules. In some case, where a module that is being used depends on an earlier version of another module, more complex queries and optimizations may be needed. For further information about how dependencies between module are represented see details of the Module dependency reference set .