I have a table like this:
My task is to prepare a query for listing KPIs and calculations for them.
-
First I tried the case when statement like this(shortened):
select case
When PROTOCOLID = 61002 AND TRANS_STATS_TYPE = 3 THEN "S11 Session Successes" When PROTOCOLID = 61002 AND TRANS_STATS_TYPE = 3 AND (CAUSE_CODE is NULL OR CAUSE_CATEGORY="S") THEN "S11 Update Successes"
When PROTOCOLID = 61002 AND TRANS_STATS_TYPE = 7 THEN "Modify Access Bearer Successes"End kpi_name, Sum(count) cnt
This brought me two problems:
1) the case-when statement stops when it meets the first condition, so it would succesfully determine and label the row as "S11 Session Successs" but it would recognize the "S11 Update Sucesses" unless I make a union and write that in another select.
2) If there are no rows with relevant conditions, the results would not display the KPI name at all. I would prefer having the row displayed with KPI name and null or 0 value. I was trying to achieve this using the if statement, but I found nesting more and more condidtions into one big if statement is rather nasty and not easily modifiable.
Is there any more elegant way of achieving these 2 that Im missing? Thank you
Aucun commentaire:
Enregistrer un commentaire