SQL Practical Guide

Search

SNOMED CT Document Library

    Loading...

Page tree

  

Current Snapshot Queries

Extracting the current snapshot from a full release table requires a query that can identify the rows with the most recent effectiveTime for each uniquely identified component or reference set member. The general form of a current snapshot view query is shown below.

General Snapshot Query - Replace full_tableName with Name of a Full Release Table
select * from full_tableName tbl
	where tbl.effectiveTime = (select max(sub.effectiveTime) from full_tableName sub 
								where sub.id = tbl.id);

Including Other Constraints in a Snapshot Query

It may be tempting to write queries that add criteria specific to a particular query within the structure of a general snapshot query. However if this is done, it must be done with care because additional conditions may cause incorrect results.

Additional criteria to be applied to snapshot view must be added to the outer query to deliver the expected results. Otherwise they may inadvertently exclude the most recent component from the snapshot and thus leading to a misleading result.

For example, in the following query the check for the active status is included in the nested query. This will lead to the most recent active version of each component being selected. The result of this query will therefore include an earlier active version of any component that is now inactive. A similar issue may also occur with other criteria1 .

General Snapshot Query - With Error Due to Added Condition in Nested Query
select * from full_tableName tbl
	where tbl.effectiveTime = (select max(sub.effectiveTime) from full_tableName sub 
								where sub.id = tbl.id and sub.active=1);

The query below corrects the error in the shown above. This query will return components that are active in the current snapshot view. It will not return any components that are inactive in the current snapshot.

General Snapshot Query for Active Components in the Snapshot
select * from full_tableName tbl
	where tbl.active=1 and tbl.effectiveTime = (select max(sub.effectiveTime) from full_tableName sub 
								where sub.id = tbl.id);

Retrospective Snapshot Queries

Queries for earlier snapshot views require an additional condition so that only versions with an effectiveTime that is equal to or earlier than (i.e. less than) the date of the snapshot. In this case, it is correct to include this condition in the nested query because the objective is to constrain the maximum effectiveTime to that the subquery returns. This ensures that the outer query does not return component versions added after the specified snapshot time.

Retrospective Snapshot Query for Snapshot as at 2019-01-31
select * from full_tableName tbl
	where tbl.effectiveTime = (select max(sub.effectiveTime) from full_tableName sub 
								where sub.id = tbl.id and sub.effectiveTime<='20190131');

Most Recent Delta View Query

The most recent delta view is usually considered to be all the rows in a table with an effectiveTime equal to the release date.2 The general form of a query for the most recent delta view is shown below. Note that this query can be applied either to full release tables or the current snapshot release tables.

Most Recent Delta View Query for 2019-07-31 release. Replace tableName with Name of a Full or Snapshot Release Table for 2019-07-31 Release
select * from tableName tbl
	where tbl.effectiveTime = '20190731';

Specified Period Delta View Query

A more general purpose approach to delta views is to include all changes between two specified dates (or times). This can be applied to the period between two releases or to a defined period during which multiple changes may have occurred to the same component. The general form of a delta view query for a specified period is shown below. 

General Most Recent Delta View Query - for Change After 2019-01-31 and On or Before 2019-07-31 
select * from full_tableName tbl
	where tbl.effectiveTime > '20190131' and tbl.effectiveTime <= '20190731' ;

Note

This query requires the effectiveTime to be greater than the startDate and less than or equal to the endDate. This avoids double counting items in two consecutive periods. This means that ranges can be specified to start on one release date and end on another release date without counting changes that occured on the first release date.

Delta View with Details of Changes

It is also possible to create an enhanced delta view that not only shows which components have changed but allows the pre-change state of that component to be seen.  From a practical perspective this simply combines a delta view for a range of dates with a retrospective snapshot view for the delta view start date.

Delta View with Details of the Component Prior to the Changes
select * from full_tableName tbl
	where tbl.effectiveTime > '20190131' and tbl.effectiveTime <= '20190731'
union
select * from full_tableName tbl
	where tbl.effectiveTime = (select max(sub.effectiveTime) from full_tableName sub 
								where sub.id = tbl.id and sub.effectiveTime<='20190131')
    and tbl.id IN (select id from full_tableName 
					where effectiveTime > '20190131' and effectiveTime <= '20190731')
order by id;


Footnotes
Ref Notes
1 For more details about potential snapshot view query errors see C.3. Common Mistakes with Snapshot Generation.
2 This interpretation of "the most recent delta" depends on the practice of periodic releases with all rows added since the last release assigned the effectiveTime of the release. However, in cases where frequent interim releases are made, it may be more accurate to consider the "the most recent delta" to consist of all rows with an effectiveTime greater than the previous release date and less than or equal to the current release date. In this case, all delta view queries would to follow the form of the Specified Date Range Delta View Query with a start and end date.


Feedback
  • No labels