none
Query returning extra rows and NULLS

    Question

  • Hi,

    I have a temp table with a following column that are needed for this query

    srvc_grouping   diag_grp_id     age_grp_Id  

    Outpatient          3                         1

    outpatient          3                           1

    inpatient            3                          1

    residential         3                           1

     

    and i have another table  called bug_maint

    srvc_sum_Id         Diag_grp_Id        age_grp_Id  budamt

    1                          3                                 1              451211.00

    1                          2                                1             5454212

    2                          3                                1               4545      

     

    So this my query that i have come up with

    ;with cte_budget as (
     select distinct rtrim(srvc_grouping) srvc,
    case when rtrim(srvc_grouping) = 'Outpatient' and b.srvc_sum_Id = 1 then b.budget_amt 
             when srvc_grouping = 'Community' and b.srvc_sum_Id = 2 then b.budget_amt 
             when srvc_grouping = 'Inpatient' and b.srvc_sum_Id = 3 then b.budget_amt 
             when srvc_grouping = 'Innovations' and b.srvc_sum_Id = 4 then b.budget_amt
             when srvc_grouping = 'ICF' and b.srvc_sum_Id = 5 then b.budget_amt 
             when srvc_grouping = 'Residential' and b.srvc_sum_Id = 7 then b.budget_amt End as budamt
    from #temp1 t,QM..bud_maint b where  t.diag_grp_Id = b.Diag_grp_Id
        and t.age_grp_Id = b.age_grp_Id = 1
        and b.ins_Id in (3)
    )
    select * from cte_budget

    but when i run this query

    srvc            budamt
    Community  NULL
    Community  45113
    Inpatient      NULL
    Inpatient       44612
    ICF              NULL  -- this return null cause there is no record for this
    residential    NULL
    residential    4512121

    I have tried inner join ,left outer join, right outer join and they dont seem to help..

     

    Any help will be appreciated..

    thanks,

    Karen

    Thursday, July 30, 2009 3:19 PM

Answers

  • You have multiple matches in the join predicates and then the CASE expression generates the NULLs for those that do not match the CASE conditions.

    Here is one method to solve this:

    ;WITH cte_budget AS (
    SELECT t.srvc_grouping AS srvc,
           b.budget_amt AS budamt
    FROM #temp1 AS t
    JOIN bud_maint AS b 
      ON t.diag_grp_Id = b.diag_grp_id
     AND t.age_grp_Id = b.age_grp_id 
    JOIN (SELECT 'Outpatient' , 1 UNION ALL
          SELECT 'Community', 2 UNION ALL
          SELECT 'Inpatient', 3 UNION ALL
          SELECT 'Innovations', 4 UNION ALL
          SELECT 'ICF', 5 UNION ALL
          SELECT 'Residential', 7) AS j (srvc_grouping, srvc_sum_id)
      ON b.srvc_sum_id = j.srvc_sum_id
     AND t.srvc_grouping = j.srvc_grouping
    WHERE t.age_grp_id = 1
      AND b.ins_id = 3    
    )
    SELECT srvc, budamt 
    FROM cte_budget;

    Plamen Ratchev
    • Marked as answer by Karenros Thursday, July 30, 2009 6:21 PM
    Thursday, July 30, 2009 3:50 PM

All replies

  • You have multiple matches in the join predicates and then the CASE expression generates the NULLs for those that do not match the CASE conditions.

    Here is one method to solve this:

    ;WITH cte_budget AS (
    SELECT t.srvc_grouping AS srvc,
           b.budget_amt AS budamt
    FROM #temp1 AS t
    JOIN bud_maint AS b 
      ON t.diag_grp_Id = b.diag_grp_id
     AND t.age_grp_Id = b.age_grp_id 
    JOIN (SELECT 'Outpatient' , 1 UNION ALL
          SELECT 'Community', 2 UNION ALL
          SELECT 'Inpatient', 3 UNION ALL
          SELECT 'Innovations', 4 UNION ALL
          SELECT 'ICF', 5 UNION ALL
          SELECT 'Residential', 7) AS j (srvc_grouping, srvc_sum_id)
      ON b.srvc_sum_id = j.srvc_sum_id
     AND t.srvc_grouping = j.srvc_grouping
    WHERE t.age_grp_id = 1
      AND b.ins_id = 3    
    )
    SELECT srvc, budamt 
    FROM cte_budget;

    Plamen Ratchev
    • Marked as answer by Karenros Thursday, July 30, 2009 6:21 PM
    Thursday, July 30, 2009 3:50 PM
  • There are many design flaws, normalization, invalid data elements, invalid UI requirements etc.
    Thursday, July 30, 2009 3:52 PM