By guest


2016-06-09 18:42:03 8 Comments

I have 4 subqueries and each of the subquery is grouped by 'Group name'. The attempt is to have each subquery as a column all grouped by the 'Group Name'. Here is the query:

select  
    coalesce(co.group_name, requests.group_Name, incidents.group_Name, problems.group_Name) as 'SD Groups'
    , isnull(co.co, '') as 'CO'
    , isnull(incidents.incidents, '' ) as 'Inc'
    , isnull(problems.problems, '') as 'Prob'
    , isnull(requests.requests, '') as 'Rqst'
from
    (
    select 
        groups.last_name AS Group_Name   
        ,count(chg_ref_num)  AS 'CO'
    from chg
    left join   ca_contact groups 
        on chg.group_id = groups.contact_uuid 
    left join   ca_contact assignee 
        on chg.assignee = assignee.contact_uuid 
    left join   ca_company cc 
        on  assignee.company_uuid = cc.company_uuid                       
    where   
        groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse') 
        and status = 'CL'
        and     convert(varchar, dateadd(hh,-8,dateadd(ss,chg.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)
        and     parent is NULL  
        and     cc.company_name = 'XYZ' 
    group by    groups.last_name
        ) as CO
full outer join 
    (
    select                         
        groups.last_name AS Group_Name   
        ,count(ref_num) AS Requests
    from call_req cr                                
    left join   ca_contact groups 
        on cr.group_id = groups.contact_uuid  
    left join   ca_contact assignee 
        on cr.assignee = assignee.contact_uuid  
    left join   ca_company cc 
        on  assignee.company_uuid = cc.company_uuid                       
    where   
        groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse')                         
        and     cr.status in ('CL')     
        and     convert(varchar, dateadd(hh,-8,dateadd(ss,cr.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)                    
        and     cr.parent is NULL   
        and     cr.type = 'R'   
        and     cc.company_name = 'XYZ'                 
    group by    groups.last_name, cr.type
        ) as Requests
    on co.group_name = requests.group_name
full outer join
    (
    select                                 
        groups.last_name AS Group_Name   
        ,count(ref_num) AS Problems
    from call_req cr                                
    left join   ca_contact groups 
        on cr.group_id = groups.contact_uuid
    left join   ca_contact assignee 
        on cr.assignee = assignee.contact_uuid  
    left join   ca_company cc 
        on  assignee.company_uuid = cc.company_uuid                               
    where   
        groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse')                         
        and     cr.status in ('CL')     
        and     convert(varchar, dateadd(hh,-8,dateadd(ss,cr.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)                    
        and     cr.parent is NULL   
        and     cr.type = 'P'   
        and     cc.company_name = 'XYZ'             
    group by    groups.last_name, cr.type
    ) as Problems
    on  requests.group_name = problems.group_name

full outer join 
    (
    select                                 
        groups.last_name AS Group_Name   
        ,count(ref_num) AS Incidents
    from call_req cr                                
    left join   ca_contact groups 
        on cr.group_id = groups.contact_uuid  
    left join   ca_contact assignee 
        on cr.assignee = assignee.contact_uuid  
    left join   ca_company cc 
        on  assignee.company_uuid = cc.company_uuid                           
    where   
        groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                        , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                        , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                        , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                        , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse')                         
        and     cr.status in ('CL')     
        and     convert(varchar, dateadd(hh,-8,dateadd(ss,cr.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)                    
        and     cr.parent is NULL   
        and     cr.type = 'I'   
        and     cc.company_name = 'XYZ'                 
    group by    groups.last_name, cr.type
    ) as Incidents
    on requests.group_name = incidents.group_name
order by 
    'SD Groups' asc  

Here are the results:

Group_Name                                     CO  Inc Prob Rqst
8197 Qlikview Support                           0   1   0   7
8202 OBIEE-BIP                                  0   4   0   11
8205 BI SAS                                     0   11  1   11
8206 BI CCBI                                   10   17  0   43
8208 BI ePlan Reporting and Support             0   0   0   4
8211 BI Data Quality                            0   0   0   12
8212 BI EPM Report Architect/Developer          0   3   1   5
8214 BI EPM User Experience                     0   2   0   0
8214 BI EPM User Experience                     0   0   1   0
8215 BI EPM OLAP Architect/Developer            0   15  0   2
8219 BI Data Warehouse                         16   71  4   13

Notice two rows for Group 8214. Each group should be represented once and the desired result for Group 8214 should be:

Group_Name                                     CO  Inc Prob Rqst
8214 BI EPM User Experience                     0   2   1   0

Is there an obvious flaw in the code? My best estimation is looking at the coalesce function - which I tried and failed. Among my attempts was this unfriendly piece:

isnull(isnull(isnull(co.group_name, requests.group_Name), incidents.group_Name), problems.group_Name) 

but the same result.

3 comments

@Andriy M 2016-06-10 02:09:46

The last three derived tables in your query are almost identical: they join the same tables in the same manner and use almost identical filters. The only difference is the cr.type condition: each subquery filters on a different value of that column.

In such circumstances, you can easily combine those three derived tables into one producing all three counts. You will just need to use conditional aggregation for that:

(
select                                 
    groups.last_name AS Group_Name   
    ,count(CASE cr.type WHEN 'R' THEN ref_num END) AS Requests
    ,count(CASE cr.type WHEN 'P' THEN ref_num END) AS Problems
    ,count(CASE cr.type WHEN 'I' THEN ref_num END) AS Incidents
from call_req cr
left join   ca_contact groups 
    on cr.group_id = groups.contact_uuid  
left join   ca_contact assignee 
    on cr.assignee = assignee.contact_uuid  
left join   ca_company cc 
    on  assignee.company_uuid = cc.company_uuid                           
where   
    groups.last_name in ('8197 Qlikview Support', '8202 OBIEE-BIP'
                    , '8205 BI SAS', '8206 BI CCBI', '8208 BI ePlan Reporting and Support'
                    , '8211 BI Data Quality', '8212 BI EPM Report Architect/Developer'
                    , '8213 BI EPM Framework Architect/Developer', '8214 BI EPM User Experience'
                    , '8215 BI EPM OLAP Architect/Developer', '8219 BI Data Warehouse')
    and     cr.status in ('CL')
    and     convert(varchar, dateadd(hh,-8,dateadd(ss,cr.close_date, '1970')), 101)  >= DATEADD(month, datediff(month, 0, getdate()), 0)
    and     cr.parent is NULL
    and     cr.type IN ('R', 'P', 'I')  -- probably redundant if cr.type cannot have other values
    and     cc.company_name = 'XYZ'                 
group by    groups.last_name
) as CR

As you can see, the specific cr.type filters are now moved to the aggregate functions in the form of CASE expressions. This way each COUNT considers only rows with a specified cr.type. Note also the absence of cr.type in the GROUP BY.

For the sake of completeness, here is also how your main SELECT would change:

SELECT
    coalesce(CO.group_name, CR.group_Name) AS [SD Groups],
    , isnull(CO.co,        '') as CO
    , isnull(CR.incidents, '') as Inc
    , isnull(CR.problems,  '') as Prob
    , isnull(CR.requests,  '') as Rqst
FROM
    ...

Given the complexity of your query, I would expect this rewrite to improve the query's performance materially. Moreover, since the number of datasets to join is now reduced to just two, your original issue, the substance of which has been explained in the answer by ypercubeᵀᴹ, is likely to go away as well.

@ypercubeᵀᴹ 2016-06-10 00:30:52

Is there an obvious flaw in the code? My best estimation is looking at the coalesce function - ...

Yes to both. One problem is that the 2nd (and 3rd) FULL JOIN use the (derived) tables' group_name columns. As a result, the 2nd and 3rd full join cancel the 1st and the 2nd full join respectively. Your query with only the basic structure, stripped from the details:

select  
    coalesce(co.group_name, requests.group_Name, incidents.group_Name, 
             problems.group_Name) as 'SD Groups'
    ---
from
    ( --- ) as CO
full outer join 
    ( --- ) as Requests
    on co.group_name = requests.group_name
full outer join
    ( --- ) as Problems
    on  requests.group_name = problems.group_name
full outer join 
    ( --- ) as Incidents
    on requests.group_name = incidents.group_name

After the 1st full join:

ON co.group_name = requests.group_name

there are possibly rows from either table (groups or requests) that don't match the other and the empty columns are filled with NULL values.

Then the 2nd full join happens:

ON requests.group_name = problems.group_name

but this ON condition will be satisfied only by rows that have non-null requests.group_name. This basically converts these 2 FULL joins to a complicated mess which is almost (but not exactly) the full join of 3 tables. Some rows will not be matched, In particular the rows from tables co and problems that have the same group_name (but no such row exists in table requests) will not be matched but will end up in different rows.

And again the 3rd full join:

ON requests.group_name = incidents.group_name

will make it even more complicated. As a result, your query is equivalent to the the union of (requests left joined to the 3 other tables) and 3 antijoins (each of the 3 tables with requests):

select  
    ---
from
    ( --- ) as Requests
left outer join
    ( --- ) as CO
    on co.group_name = requests.group_name
left outer join
    ( --- ) as Problems
    on  requests.group_name = problems.group_name
left outer join 
    ( --- ) as Incidents
    on requests.group_name = incidents.group_name

union all 

select  
    ---
from
    ( --- ) as CO
left outer join
    ( --- ) as Requests
    on co.group_name = requests.group_name
where 
    requests.group_name is null

union all 

select  
    ---
from
    ( --- ) as Problems
left outer join
    ( --- ) as Requests
    on problems.group_name = requests.group_name
where 
    requests.group_name is null

union all 

select  
    ---
from
    ( --- ) as Incidents
left outer join
    ( --- ) as Requests
    on incidents.group_name = requests.group_name
where 
    requests.group_name is null

That is the reason for the same group_name appearing in two rows. You can see that this happens only when the CO has no rows with such value but the other tables have.


If you want to really have 3 FULL joins, the ON conditions should be rewritten with either ISNULL or COALESCE:

select  
    coalesce(co.group_name, requests.group_Name, incidents.group_Name, 
             problems.group_Name) as [SD Groups]
    ---
from
    ( ---
    ) as CO
full outer join 
    ( ---
    ) as Requests
    on co.group_name = requests.group_name
full outer join
    ( ---
    ) as Problems
    on coalesce(co.group_name, requests.group_name) 
       = problems.group_name
full outer join 
    ( ---
    ) as Incidents
    on coalesce(co.group_name, requests.group_name, problems_group_name) 
       = incidents.group_name
order by 
    --- ;

Other minor issues is the use of 'single quotes' for the aliases. You should either use the standard "double quotes" or the SQL Server's [square brackets]. Or even better don't quote your identifiers and keep them without spaces and other weird characters. Single quoted strings should be used only for string literals, not for identifiers.

@Joe 2016-06-09 18:54:43

You have cr.type in some of your group by. You need to remove this to avoid getting duplicates. I do not see this in the select lists so I dont think you need it in the group by.

@Andriy M 2016-06-10 01:38:44

cr.type is redundant in each of the three GROUP BYs where it's encountered, there's no doubt about that, but it's not causing any duplication, because in each case the subquery's filtering the results on a specific value of that column. Thus, each group contains the same cr.type value, so groups.last_name is never repeated.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] full outer join not working

  • 2015-09-19 00:33:43
  • Anne Ortiz
  • 3905 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql join

1 Answered Questions

[SOLVED] Unable to run FULL OUTER JOIN

5 Answered Questions

[SOLVED] Full Outer Join in MS Access

1 Answered Questions

[SOLVED] Full Outer Join With Where Clause

2 Answered Questions

[SOLVED] Full outer join in MySql

2 Answered Questions

1 Answered Questions

[SOLVED] Full outer join problems

1 Answered Questions

[SOLVED] Right Full Outer Join Query

Sponsored Content