Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2012
    Posts
    9

    Unanswered: help with multi-case query

    If I have the following query:

    select distinct TO_CHAR(ARCHIVE_DATE, 'mm/dd/yyyy') as "Date"
    ,CASE APS_RULE_ID WHEN '67.2' THEN count (distinct (APS_TX_ID)) END as "Rule 67.2"
    --
    ,CASE APS_RULE_ID WHEN '34.4' THEN count (distinct (APS_TX_ID)) END as "Rule 34.4"
    ,CASE APS_RULE_ID WHEN '34.2' THEN count (distinct (APS_TX_ID)) END as "Rule 34.2"
    ,CASE APS_RULE_ID WHEN '34.0' THEN count (distinct (APS_TX_ID)) END as "Rule 34.0"
    ,CASE APS_RULE_ID WHEN '162.1' THEN count (distinct (APS_TX_ID)) END as "Rule 162.1"
    ,CASE APS_RULE_ID WHEN '162.2' THEN count (distinct (APS_TX_ID)) END as "Rule 162.2"
    ,CASE APS_RULE_ID WHEN '162.3' THEN count (distinct (APS_TX_ID)) END as "Rule 162.3"
    ,CASE APS_RULE_ID WHEN '162.4' THEN count (distinct (APS_TX_ID)) END as "Rule 162.4"
    ,CASE APS_RULE_ID WHEN '162.5' THEN count (distinct (APS_TX_ID)) END as "Rule 162.5"
    ,CASE APS_RULE_ID WHEN '162.6' THEN count (distinct (APS_TX_ID)) END as "Rule 162.6"
    ,CASE APS_RULE_ID WHEN '166.0' THEN count (distinct (APS_TX_ID)) END as "Rule 166.0"
    ,CASE APS_RULE_STATUS WHEN 'Limited' THEN count (distinct (APS_TX_ID)) END as "Rule 44.0 Limited Risk"
    ,CASE APS_RULE_STATUS WHEN 'Moderate' THEN count (distinct (APS_TX_ID)) END as "Rule 44.0 Moderate Risk"
    ,CASE APS_RULE_STATUS WHEN 'High' THEN count (distinct (APS_TX_ID)) END as "Rule 44.0 High Risk"
    from ARCHV_SCORE_RESULT
    Where ARCHIVE_DATE > to_date('04-29-2013 23:59:00','mm-dd-yyyy hh24:mi:ss')
    and APS_RULE_STATUS != 'Pass'
    Group by TO_CHAR(ARCHIVE_DATE, 'mm/dd/yyyy'), APS_RULE_ID, APS_RULE_STATUS
    Order by "Date" DESC;


    and if I currently get a record set with multiple rows for each date with sum count total in each particular rule column, and null values in the rest of the columns, how can I get one record per actual date with sum count totals for every rule included across the board??

    Any help would be greatly appreciated??

    Thank you,

    Patrick Q

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try something like this...
    Code:
    select TO_CHAR(ARCHIVE_DATE , 'mm/dd/yyyy') as "Date"
         , count( distinct CASE APS_RULE_ID WHEN  '67.2' THEN APS_TX_ID END ) as "Rule 67.2" 
         , count( distinct CASE APS_RULE_ID WHEN  '34.4' THEN APS_TX_ID END ) as "Rule 34.4"
         , count( distinct CASE APS_RULE_ID WHEN  '34.2' THEN APS_TX_ID END ) as "Rule 34.2"
         , count( distinct CASE APS_RULE_ID WHEN  '34.0' THEN APS_TX_ID END ) as "Rule 34.0"
         , count( distinct CASE APS_RULE_ID WHEN '162.1' THEN APS_TX_ID END ) as "Rule 162.1"
         , count( distinct CASE APS_RULE_ID WHEN '162.2' THEN APS_TX_ID END ) as "Rule 162.2"
         , count( distinct CASE APS_RULE_ID WHEN '162.3' THEN APS_TX_ID END ) as "Rule 162.3"
         , count( distinct CASE APS_RULE_ID WHEN '162.4' THEN APS_TX_ID END ) as "Rule 162.4"
         , count( distinct CASE APS_RULE_ID WHEN '162.5' THEN APS_TX_ID END ) as "Rule 162.5"
         , count( distinct CASE APS_RULE_ID WHEN '162.6' THEN APS_TX_ID END ) as "Rule 162.6"
         , count( distinct CASE APS_RULE_ID WHEN '166.0' THEN APS_TX_ID END ) as "Rule 166.0"
         , count( distinct CASE APS_RULE_STATUS WHEN 'Limited'  THEN APS_TX_ID END ) as "Rule 44.0 Limited Risk"
         , count( distinct CASE APS_RULE_STATUS WHEN 'Moderate' THEN APS_TX_ID END ) as "Rule 44.0 Moderate Risk"
         , count( distinct CASE APS_RULE_STATUS WHEN 'High'     THEN APS_TX_ID END ) as "Rule 44.0 High Risk"
     from  ARCHV_SCORE_RESULT
     Where ARCHIVE_DATE    >= to_date('04-30-2013 00:00:00' , 'mm-dd-yyyy hh24:mi:ss')
       and APS_RULE_STATUS <> 'Pass'
     Group by
           TO_CHAR(ARCHIVE_DATE , 'mm/dd/yyyy')
     Order by
           "Date" DESC
    ;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •