Queries to help find and fix possible errors related to SRANK Review Date.
(Updated 9/19/2016 by Lynn Kutner)
The following are modified from queries provided by Randi Mulder of the Yukon Conservation Data Centre.
The idea here is to catch instances where the SRANK Review Date field has not been updated when the SRANK has changed and the SRANK Change Date has been updated. Presumably, in order to make that change, the SRANK was reviewed, and the review date should have been updated as well. The methodology there indicates that the review date should never be older than the change date. These queries offer a way to find and fix these issues.
1. Query to find Subnational Elements where the srank review date is null but there is a value in the srank change date
SELECT est.ELEMENT_SUBNATIONAL_ID est_id,
est.S_PRIMARY_COMMON_NAME scomname,
SCIENTIFIC_NAME.SCIENTIFIC_NAME sname,
est_elcode(est.element_subnational_id) elcode,
est.S_RANK,
est.S_RANK_CHANGE_DATE,
est.S_RANK_REVIEW_DATE
FROM ELEMENT_SUBNATIONAL EST, SCIENTIFIC_NAME
WHERE est.SNAME_ID = SCIENTIFIC_NAME.SCIENTIFIC_NAME_ID
and est.S_RANK_CHANGE_DATE is not null
and est.S_RANK_REVIEW_DATE is null
2. Update query (can be run in TOAD, TOra, SQL Developer, or SQL Plus while logged in as the biotics_dlink user) to update the srank review date from null to whatever the srank change date is (only for those Elements determined above):
update ELEMENT_SUBNATIONAL
set S_RANK_REVIEW_DATE=S_RANK_CHANGE_DATE, REC_LAST_MOD_USER='{your username}'
where ELEMENT_SUBNATIONAL_ID in ({your list of element subnational IDs});
3. Query to find Subnational Elements where the srank review date is older than the srank change date:
SELECT est.ELEMENT_SUBNATIONAL_ID,
est.S_PRIMARY_COMMON_NAME scomname,
SCIENTIFIC_NAME.SCIENTIFIC_NAME sname,
est_elcode(est.element_subnational_id) elcode,
est.S_RANK,
est.S_RANK_CHANGE_DATE,
est.S_RANK_REVIEW_DATE
FROM ELEMENT_SUBNATIONAL est, SCIENTIFIC_NAME
WHERE est.SNAME_ID = SCIENTIFIC_NAME.SCIENTIFIC_NAME_ID
/* checks for CHANGE dates that are more than 2 months newer than the REVIEW date; you can edit the number at the end to change the number of months */
and months_between(est.S_RANK_CHANGE_DATE, est.S_RANK_REVIEW_DATE) > 2
4. Run update query (see #2 above) to update the rank review dates to the rank change date for those Elements.