CODEWITHSUNDEEP
sqlsql-server-2008-r2
GunslingerFyre
GunslingerFyre

Reputation: 67

How to join and total two different tables into a third table

I have two tables with data, InboundCalls and OutboundCalls, like so:

InboundCalls I
Date        System    VDN      VDNName    GroupID    GroupName   AllCalls    RecordedCalls
01/08/2015  1         2750001  Sales      1          AllUsers    50          49
01/08/2015  1         2750001  CustServ   1          AllUsers    25          25
01/08/2015  1         2750001  CustServ   2          Supervisors 10          9
01/08/2015  2         2851234  Payments   1          AllUsers    30          29

OutboundCalls O
Date        System    GroupID    GroupName    AllCalls    RecordedCalls
01/08/2015  1         1          AllUsers     65          65
01/08/2015  1         2          Supervisors  20          19
01/08/2015  2         1          AllUsers     30          30

What I'm trying to accomplish is creating a third table that joins these two together, JoinedCalls, and totals AllCalls and RecordedCalls for each Date, System, and GroupID. One of the challenges is that GroupID is specific to the System, so there are multiple GroupID = 1, GroupID = 2, and so on. Also, InboundCalls I has additional columns that OutboundCalls O doesn't have, VDN and VDNName. There can be multiple GroupIDs that take calls from VDN 275001, and calls from multiple VDNs that are handled by the same GroupID. I need to total all the calls for Date 01/08/2015, System 1, and GroupID 1. Then total all the calls for Date 01/08/2015, System 1, GroupID 2. Then Date 01/08/2015, System 2, GroupID 1. And so forth.

I know I need to leave out VDN and VDNName from the query, since it doesn't exist in both tables. I've tried two selects and UNION ALL, but it appears that I get rows for each combination of Date, VDN, and GroupID, even though I'm not selecting VDN. I'm also using GROUP BY Date, System, GroupID, so I thought it would combine them. EDIT: I've also tried using SUM(I.AllCalls + O.AllCalls), SUM(I.RecordedCalls + O.RecordedCalls), but that resulted in a crazy high count for both.

Desired output from sample data above:

JoinedCalls J
Date        System    GroupID    GroupName    AllCalls    RecordedCalls
01/08/2015  1         1          AllUsers     140         139
01/08/2015  1         2          Supervisors  30          28
01/08/2015  2         1          AllUsers     60          59

I'm self-taught in SQL, which means there are definitely huge gaps in my knowledge. Please let me know if more information is needed, and thanks in advance for your help!

Upvotes: 1

Views: 63

Answers (1)

Andrey Morozov
Andrey Morozov

Reputation: 7969

Try this:

create table InboundCalls (
    [Date] date,       
    [System] int,    
    VDN int,     
    VDNName varchar(50),   
    GroupID int,    
    GroupName varchar(50),  
    AllCalls int,    
    RecordedCalls int
);

create table OutboundCalls (
    [Date] date,       
    [System] int,      
    GroupID int,    
    GroupName varchar(50),  
    AllCalls int,    
    RecordedCalls int
);

insert into InboundCalls (
[Date], [System], VDN, VDNName, GroupID, GroupName, AllCalls, RecordedCalls) 
values
('01/08/2015', 1, 2750001, 'Sales', 1, 'AllUsers', 50, 49),
('01/08/2015', 1, 2750001, 'CustServ', 1, 'AllUsers', 25, 25),
('01/08/2015', 1, 2750001, 'CustServ', 2, 'Supervisors', 10, 9),
('01/08/2015', 2, 2851234, 'Payments', 1, 'AllUsers', 30, 29);

insert into OutboundCalls (
[Date], [System],  GroupID, GroupName, AllCalls, RecordedCalls) values
('01/08/2015',  1, 1, 'AllUsers', 65, 65),
('01/08/2015',  1, 2, 'Supervisors', 20, 19),
('01/08/2015',  2, 1, 'AllUsers', 30, 30);

select [date]
, [system]
, groupid
, groupname
, sum (allcalls) [allcalls]
, sum(recordedcalls) [recordedcalls]
from (
    select [date], [system], groupid, groupname, allcalls, recordedcalls
    from inboundcalls
    union all
    select [date], [system], groupid, groupname, allcalls, recordedcalls
    from outboundcalls
) as t
group by [date], [system], groupid, groupname;

Result

date        system  groupid groupname   allcalls    recordedcalls
-----------------------------------------------------------------
2015-01-08  1       1       AllUsers    140         139
2015-01-08  1       2       Supervisors 30          28
2015-01-08  2       1       AllUsers    60          59

Upvotes: 3

Related Questions