Merge two SELECT queries with different WHERE clauses

  • I have one table of services. I need to merge two SELECT queries. Both have different where clauses. For example

    SELECT 
      U_REGN as 'Region', 
      COUNT(callID) as 'OpenServices',
      SUM(CASE WHEN descrption LIKE '%DFC%' THEN 1 ELSE 0 END) 'DFC'
    FROM OSCL
    WHERE     
      ([status] = - 3) 
    GROUP BY 
      U_REGN
    ORDER BY 
      'OpenServices' desc
    

    This gives me result

    Region    | OpenServices | DFC
    Karaci    | 14           | 4
    Lahore    | 13           | 3
    Islamabad | 10           | 4
    

    I have another query

    SELECT 
      U_REGN as 'Region', 
      COUNT(callID) as 'ClosedYesterday'
    FROM OSCL
    WHERE 
      DATEDIFF(day, closeDate, GETDATE()) = 1
    GROUP BY 
      U_REGN
    ORDER BY 
      'ClosedYesterday' desc
    

    It gives me result

    Region    | ClosedServices
    Karachi   | 8
    Lahore    | 7
    Islamabad | 4
    

    I need to merge both results, and show ClosedServices beside the DFC column.

    There's an inconsistency - your second query produces a column called ClosedYesterday but the example data says ClosedServices.

    What does "merge" mean?

  • Treat your two current queries' resultsets as tables and join them:

    select
        FirstSet.Region,
        FirstSet.OpenServices,
        FirstSet.DFC,
        SecondSet.ClosedYesterday
    from 
    (
        SELECT U_REGN as 'Region', COUNT(callID) as 'OpenServices',
        SUM(CASE WHEN descrption LIKE '%DFC%' THEN 1 ELSE 0 END) 'DFC'
        FROM OSCL 
        WHERE ([status] = - 3) 
        GROUP BY U_REGN 
        --ORDER BY 'OpenServices' desc
    ) as FirstSet
    inner join
    (
        SELECT U_REGN as 'Region', 
        COUNT(callID) as 'ClosedYesterday'
        FROM OSCL
        WHERE DATEDIFF(day, closeDate, GETDATE()) = 1
        GROUP BY U_REGN
        --ORDER BY 'ClosedYesterday' desc
    ) as SecondSet
    on FirstSet.Region = SecondSet.Region
    order by FirstSet.Region
    

    Not the prettiest bit of SQL I've ever written but hopefully you'll see how it works and understand how to maintain it.

    I suspect a better-performing query would be a single SELECT from OSCL, grouped by U_REGN, with each of your three counters as separate SUM(CASE ...) statements akin to what you do currently for DFC. This will be a single table scan, at most, depending you your indexes & schema.

    What happens when there's results in one subquery that aren't there in the other? I'd suspect you actually want a full outer join here.

    @Simon - fair point, but that wasn't the OP's given scenario.

    Thank You, this is what I wanted, Thanks! And also Thanks @SimonRigharts . Inner join wasnt showing me all results I intended, so I Used full outer join, works perfectly :)

  • Building off Michael's suggestion:

    SELECT
        U_REGN AS 'Region',
        SUM(CASE WHEN [status] = -3 THEN 1 ELSE 0 END) AS 'OpenServices',
        SUM(CASE WHEN [status] = -3 AND [description] LIKE '%DFC%' THEN 1 ELSE 0 END) AS 'DFC',
        SUM(CASE WHEN DATEDIFF(day, closeDate, GETDATE()) = 1 THEN 1 ELSE 0 END) AS 'ClosedYesterday'
    FROM
        OSCL
    GROUP BY 
        U_REGN
    ORDER BY
        'OpenServices' desc
    

    Thank You Simon, but I used @Michael Green's query with full outer join because this query gives me even those regions which doesnt have any open or any closed service!

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM