SQL Queries - CAST Central Base - Queries on snapshots - How to check which quality rules have been ADDED and DELETED between two snapshots

Created by John McMahon on Feb 11, 2022
Purpose of Query

The purpose of this page is to provide a query that can list the metrics which have been added and deleted between two snapshots

Applicable CAST Version
Release
Yes/No
8.2.x(tick)
8.1.x(tick)
8.0.x(tick)
Applicable RDBMS
RDBMS
Yes/No
Oracle Server(question)
Microsoft SQL Server(question)
CSS2(tick)
Query for CSS

Two pieces of information are needed for this query. The current snapshot id and the prior snapshot id. These values can be obtained on this page: SQL Queries - CAST Central Base - Queries on snapshots - How to check the status of a snapshot

Once the values are obtained, then you can substitute them in the query below (be careful to substitute correctly to get the proper results)

SELECT 'LOST'          ,
       SNAP2.METRIC_ID ,
       (SELECT METRIC_DESCRIPTION
       FROM    dss_metric_descriptions
       WHERE   metric_id           = SNAP2.METRIC_ID
       AND     language            = 'ENGLISH'
       AND     description_type_id = 0
       )
       ,
       (SELECT metric_critical
       FROM    dss_metric_type_trees
       WHERE   metric_id = SNAP2.METRIC_ID
       )
       CRITICAL ,
       (SELECT aggregate_weight
       FROM    dss_metric_type_trees
       WHERE   metric_id = SNAP2.METRIC_ID
       )
       WEIGHT
FROM   (SELECT DISTINCT(metric_id)
       FROM             dss_metric_results
       WHERE            snapshot_id=<prior SNAPSHOT id>
       )
       SNAP2
WHERE  SNAP2.METRIC_ID NOT IN
                               (SELECT DISTINCT(metric_id)
                               FROM             dss_metric_results
                               WHERE            snapshot_id=<CURRENT SNAPSHOT id>
                               )
AND    SNAP2.METRIC_ID%2 = 0

UNION

SELECT 'ADDED'         ,
       SNAP3.METRIC_ID ,
       (SELECT METRIC_DESCRIPTION
       FROM    dss_metric_descriptions
       WHERE   metric_id           = SNAP3.METRIC_ID
       AND     language            = 'ENGLISH'
       AND     description_type_id = 0
       )
       ,
       (SELECT metric_critical
       FROM    dss_metric_type_trees
       WHERE   metric_id = SNAP3.METRIC_ID
       )
       CRITICAL ,
       (SELECT aggregate_weight
       FROM    dss_metric_type_trees
       WHERE   metric_id = SNAP3.METRIC_ID
       )
       WEIGHT
FROM   (SELECT DISTINCT(metric_id)
       FROM             dss_metric_results
       WHERE            snapshot_id=<CURRENT SNAPSHOT id>
       )
       SNAP3
WHERE  SNAP3.METRIC_ID NOT IN
                               (SELECT DISTINCT(metric_id)
                               FROM             dss_metric_results
                               WHERE            snapshot_id=<prior SNAPSHOT id>
                               )
AND    SNAP3.METRIC_ID%2 = 0;
Query result example

"ADDED";552;"Method naming convention - case control";0;6

"LOST";7912;"Avoid unreferenced Data Members";0;4

Query result interpretation
 Rows will list whether the metric has been added (new to the current snapshot as compared to the prior one) or lost (in the prior snapshot, but not in the current one) and some details on the metric.
Query for Oracle
Enter the SQL query
Query result example
 
Query result interpretation
 
Query for SQL server
Enter the SQL query
Query result example
 
Query result interpretation
 
Notes/comments
 

 

 

Related Pages