Eggchaser Eggchaser - 18 days ago 8
SQL Question

Querying 2 Views on a Join - answers on one half being duplicated

I have 2 views one holding inbound calls and the other outbound calls. I want my query to join the 2 views so that the inbound and outbound stand side by side for each operator (destinationname and originationname). At the moment my current query duplicates one half of the join, in the example below the inbound.

SELECT i.destinationname, i.volumein as inbound, o.volumeout as outbound,
i.year, i.month
FROM InboundCalls i
inner join OutboundCalls o
on i.destinationname = o.originationname
GROUP BY i.year, i.month, i.destinationname, o.volumeout, i.volumein


DestinationName Inbound Outbound Year Month
Accounts Spare 9 33 2016 8
Accounts Spare 9 9 2016 8
Accounts Spare 9 7 2016 8
Accounts Spare 9 38 2016 8
Accounts Spare 21 33 2016 9
Accounts Spare 21 9 2016 9
Accounts Spare 21 7 2016 9
Accounts Spare 21 38 2016 9


The result I am looking for will be similar to the below;

DestinationName Inbound Outbound Year Month
Accounts Spare 84 210 2016 9
Accounts Spare 12 32 2016 11
Accounts Spare 36 103 2016 10
Steve Jones 36 96 2016 8
Wayne Rooney 162 172 2016 8
Alan Shearer 1 216 2016 9
Alan Shearer 74 82 2016 8


Please let me know if this needs clarifying.

Answer

The reason for wrong results is that you join only on destination name, not on year and month.

First, you need to join not only on DestinationName but on the Year and Month as well. If the views have one row per distinct destination name, year and month, then you can get rid of the GROUP BY as well.

Second, you probably need a FULL JOIN instead of an INNER JOIN, assuming that you want results when there are only Incoming but not Outgoing data (and vice versa) for some month.

SELECT 
    COALESCE(i.destinationname, o.destinationname) AS DestinationName 
    COALESCE(i.volumein, 0)    AS InBound, 
    COALESCE(o.volumeout, 0)   AS OutBound,
    COALESCE(i.year, o.year)   AS Year, 
    COALESCE(i.month, o.month) AS Month
FROM InboundCalls AS i
     FULL JOIN OutboundCalls AS o
     ON  i.destinationname = o.originationname
     AND i.year = o.year
     AND i.month = o.month ;