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

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

ReleaseYes/No
8.3.x✅
8.2.x✅
8.1.x✅
8.0.x✅

Applicable RDBMS

RDBMSYes/No
Oracle Server❌
Microsoft SQL Server❌
CSS3✅
CSS2✅

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