Search the Community
Showing results for tags 'subqueries'.
Found 1 result
-
I am trying to calculate a metric to show the average percentage of incidents associated with changes by dividing 'the number of incidents linked to changes' by 'the total number of changes' and multiplying the result by 100. I would like to show this in a widget and using a count list I am able to calculate the following. Number of incidents linked to changes: "SELECT COUNT h_sm_relationship_entities.h_entity_id FROM h_itsm_requests,h_sm_relationship_entities WHERE (h_itsm_requests.h_pk_reference = h_sm_relationship_entities.h_linked_entity_id) AND (h_sm_relationship_entities.h_linked_entity_id LIKE 'IN%') AND (h_sm_relationship_entities.h_entity_id LIKE 'CH%')" Total number of changes: "SELECT COUNT h_itsm_requests.h_pk_reference FROM h_itsm_requests WHERE h_itsm_requests.h_pk_reference LIKE 'CH%'" But the metric for this is difficult. I currently have the following but the system doesn't like it: "( (SELECT COUNT h_sm_relationship_entities.h_entity_id FROM h_itsm_requests,h_sm_relationship_entities WHERE (h_itsm_requests.h_pk_reference = h_sm_relationship_entities.h_linked_entity_id) AND (h_sm_relationship_entities.h_linked_entity_id LIKE 'IN%') AND (h_sm_relationship_entities.h_entity_id LIKE 'CH%')) DIV (SELECT COUNT h_itsm_requests.h_pk_reference FROM h_itsm_requests WHERE h_itsm_requests.h_pk_reference LIKE 'CH%') ) * 100" If anyone could offer some advice, or point me in the right direction if I am barking up the wrong tree, then please let me know. Cheers Tom
