SQL Queries - CAST Central Base - Queries on snapshots - How to get the variation between two snapshots for TQI and LOC

Created by John McMahon on Feb 11, 2022


Purpose of Query

 This page provides a query that will provide the variation between 2 snapshots for the following metrics:

  • Total Quality Index (TQI)
  • Line of Code (LOC)
  • Number of Critical Violations (CV)
  • Unadjusted Data Functions
  • Unadjusted Transaction Functions
  • OMG-Compliant Automated Function Points

This query allows for quick comparison between two snapshots for these main metrics so that one could do a rough validation of the most recent snapshot to make sure that there is not a major issue with it such as missing code, or a problem with an analysis., or other issues.

Applicable CAST Version


Release
Yes/No
8.3.x (tick)
8.2.x (tick)
8.1.x (tick)
8.0.x (tick)
Applicable RDBMS

 

RDBMS
Yes/No
Oracle Server (error)
Microsoft SQL Server (error)
CSS3(tick)
CSS2 (tick)


Query for CSS

In the below query, add the value for snapshot_id for <current_snapshot> and <previous_snapshot>. To get the snapshot_id, see this page: SQL Queries - CAST Central Base - Queries on snapshots - How to get the ID and name of a snapshot for a given application

SELECT prev_snap.metric_id                   ,
       prev_snap.metric_name                 ,
       prev_snap.metric_num_value AS previous,
       curr_snap.metric_num_value AS CURRENT ,
       CASE
              WHEN prev_snap.metric_num_value <> 0
              THEN ROUND(((curr_snap.metric_num_value - prev_snap.metric_num_value) * 100 / prev_snap.metric_num_value),2)
              ELSE 0
       END varPercent
FROM   ( SELECT dmt.metric_id      ,
               dmt.metric_name     ,
               dmr.metric_num_value,
               metric_value_index
       FROM    dss_metric_results dmr,
               dss_metric_types dmt  ,
               dss_objects o
       WHERE   dmr.metric_id    = dmt.metric_id
       AND     dmr.object_id    = o.object_id
       AND     o.object_type_id = -102
       AND     snapshot_id      = <current_snapshot>
       AND
               (
                       dmr.metric_value_index = 0
               AND     dmr.metric_id IN (60017)
               OR      dmr.metric_value_index = 1
               AND     dmr.metric_id IN (67011,10151,10203,10204,10202)
               )
       )
       curr_snap                   ,
       ( SELECT dmt.metric_id      ,
               dmt.metric_name     ,
               dmr.metric_num_value,
               metric_value_index
       FROM    dss_metric_results dmr,
               dss_metric_types dmt  ,
               dss_objects o
       WHERE   dmr.metric_id    = dmt.metric_id
       AND     dmr.object_id    = o.object_id
       AND     o.object_type_id = -102
       AND     snapshot_id      = <previous_snapshot>
       AND
               (
                       dmr.metric_value_index = 0
               AND     dmr.metric_id IN (60017)
               OR      dmr.metric_value_index = 1
               AND     dmr.metric_id IN (60017,67011,10151,10203,10204,10202)
               )
       )
       prev_snap
WHERE  curr_snap.metric_id         =prev_snap.metric_id
AND    curr_snap.metric_value_index=prev_snap.metric_value_index
Query result example
 10151;"Number of Code Lines";47;21;-55.32

10203;"Unadjusted Data Functions";0;0;0
10204;"Unadjusted Transactional Functions";0;0;0
10202;"OMG-Compliant Automated Function Points";0;0;0
60017;"Total Quality Index";3.61114900174538;3.72761018681661;3.23
67011;"Number of violations to critical quality rules";0;0;0

Query result interpretation
 The query provides the metric id, metric name, current snapshot value, previous snapshot value, and variation for the metrics described at the top of the page.


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