Need help getting SUM data from sql inner joins

Question

I have 3 tables with inner join connections from dbo.Associates to (by associateId) dbo.Relocations to (by relocationId) dbo.Expenses. Below are the tables:

-- Microsoft SQL Server 2017

CREATE TABLE Associates(
    AssociateId varchar(150) not null PRIMARY KEY,
    FirstName varchar(150) not null,
    LastName varchar(150) not null
)

INSERT INTO Associates(AssociateId, FirstName, LastName)
VALUES
('1', 'Tom', 'Tom'),
('2', 'Sue', 'Sue')

CREATE TABLE Relocations(
    RelocationId int not null PRIMARY KEY,
    AssociateId varchar(150) FOREIGN KEY REFERENCES Associates(AssociateId),
    StartingLocation varchar(150),
    EndingLocation varchar(150),
    PlanningCenter varchar(150),
    CostCenter varchar(150),
    OpenDate datetime,
    CloseDate datetime,
    LimitedAmount decimal(18,2),
    RelocationTypeId varchar(150)
)

INSERT INTO Relocations(RelocationId, AssociateId, StartingLocation, 
EndingLocation, PlanningCenter, CostCenter,
OpenDate, CloseDate, RelocationTypeId)
VALUES
(1, '2', 'Jacksonville', 'Macon', 'Buffalo', 'A', '2020-10-20', '2020-10-22', 
'Full Relocation'),
(2, '2', 'Los Angelos', 'New York', 'Detroit', 'B', '2020-09-20', null, 'Full 
Relocation'),
(3, '1', 'Washington DC', 'Houston', 'Dalls', 'C', '2020-08-08', '2020-08- 
15', 'Full Relocation'),
(4, '1', 'Lakeland', 'Atlanta', 'Seattle', 'D', '2020-09-12', '2020-09-28', 
'Full Relocation'),
(5, '1', 'San Diego', 'Woodbury', 'Baltimore', 'E', '2020-10-02', '2020-10- 
17', 'Full Relocation')

CREATE TABLE Expenses(
    ExpenseId int not null PRIMARY KEY,
    RelocationId int FOREIGN KEY REFERENCES Relocations(RelocationId),
    ExepnseTypeId varchar(150),
    DateIncurred datetime,
    GLAccountId varchar(150),
    TaxableAmount decimal(18,2),
    VoucherNumber int,
    Amount decimal(18,2),
    ToBeRepaid decimal(18,2),
    ReportableAmount decimal(18,2),
    LastUpdated datetime
)

INSERT INTO Expenses(ExpenseId, RelocationId, ExepnseTypeId, DateIncurred, 
GLAccountId, TaxableAmount, VoucherNumber,
Amount, ToBeRepaid, ReportableAmount, LastUpdated)
VALUES
(1, 1, 'Item 1', '2020-01-15', 1, 30.30, 23, 500.00, 400.00, 600.00, '2020- 
01-15'),
(2, 1, 'Item 1', '2020-01-20', 2, null, 23, 300.00, 0.00, 300.00, '2020-01- 
20'),
(3, 2, 'Item 2', '2020-02-23', 1, null, 23, 0.00, 0.00, 45.45, '2020-02-23'),
(4, 2, 'Item 3', '2020-03-11', 1, 400.00, 33, 75.00, 80.00, 0.00, '2020-03- 
11'),
(5, 3, 'Item 4', '2020-04-12', 2, 123.23, 33, 2000.50, 76.76, 540.54, '2020- 
04-12'),
(6, 3, 'Item 4', '2020-04-23', 2, null, 33, 1500.00, 0.00, 300.21, '2020-04- 
23'),
(7, 4, 'Item 5', '2020-05-03', 1, 40.00, 43, 43.00, 0.00, 43.56, '2020-05- 
03'),
(8, 4, 'Item 6', '2020-10-15', 2, null, 43, 0.00, 50.55, 76.87, '2020-10- 
15'),
(9, 5, 'Item 7', '2020-10-13', 1, 55.00, 43, 0.00, 0.00, 0.00, '2020-10-13'),
(10, 5, 'Item 8', '2020-10-05', 2, null, 53, 400.00, 0.00, 0.00, '2020-10- 
05')
INSERT INTO Expenses(ExpenseId, RelocationId, ExepnseTypeId, DateIncurred, 
GLAccountId, TaxableAmount, VoucherNumber,
Amount, ToBeRepaid, ReportableAmount, LastUpdated)
VALUES
(11, 5, 'Item 1', '2020-05-17', 2, 25, 53, 150.00, 300.00, 700.00, '2020-05- 
17')

Ultimately I am trying to generate an out put that matches this:

 OpenDate    CloseDate    Account   LastName   FirstName   Amount   Item1   Item2   Item3   Item4    Item5   Item6   Item7   Item8  
------------ ------------ --------- ---------- ----------- -------- ------- ------- ------- -------- ------- ------- ------- ------- 
10/20/2020   10/22/2020         1   Sue        Sue            500   500     null    null    null     null    null    null    null   
10/20/2020   10/22/2020         2   Sue        Sue            300   300     null    null    null     null    null    null    null  
9/20/2020    NULL               1   Sue        Sue             75   null    null    75      null     null    null    null    null   
8/8/2020     8/15/2020          2   Tom        Tom         3500.5   null    null    null    3500.5   null    null    null    null   
9/12/2020    9/28/2020          1   Tom        Tom             43   null    null    null    null     43      null    null    null   
9/12/2020    9/28/2020          2   Tom        Tom              0   null    null    null    null     null    null    null    null   
10/2/2020    10/17/2020         1   Tom        Tom              0   null    null    null    null     null    null    null    null   
10/2/2020    10/17/2020         2   Tom        Tom            550   150     null    null    null     null    null    null    400

In the below query I am able to get the total amount sum of expenses for each associates relocations and also by the GLAccount(field in the expense table) as seen below:

select 
    OpenDate,
    CloseDate,
    GLAccountId as Account,
    LastName,
    FirstName,
    SUM(t3.Amount) as Amount
from dbo.Associates t1
inner join dbo.Relocations t2
on t1.AssociateId = t2.AssociateId
inner join dbo.Expenses t3
on t2.RelocationId = t3.RelocationId
group by 
OpenDate,
CloseDate,
GLAccountId,
LastName,
FirstName

The above query gets me the first 6 fields in the desired output. But I dont know how to get the remaining Item fields

You are looking for a pivot query. But since the elements in the rows don't have a fixed name (assuming you have more than 8 items on it) you will need it to be dynamically generated. Here is your question kinda answered: https://stackoverflow.com/a/10404455/460557 — Jorge Campos, Oct 26 '20 at 17:13

GMB · Answer 1 · 2020-10-26T17:26:30.430

For a fixed list of items, you can use conditional aggregation:

select 
    r.OpenDate,
    r.CloseDate,
    e.GLAccountId as Account,
    a.LastName,
    a.FirstName,
    sum(e.Amount) as Amount,
    sum(case when e.ExpenseTypeId = 'Item 1' then e.amount end) item1,
    sum(case when e.ExpenseTypeId = 'Item 2' then e.amount end) item2,
    sum(case when e.ExpenseTypeId = 'Item 3' then e.amount end) item3,
    ...
    sum(case when e.ExpenseTypeId = 'Item 8' then e.amount end) item8
from dbo.Associates a
inner join dbo.Relocations r on r.AssociateId = a.AssociateId
inner join dbo.Expenses    e on e.RelocationId = r.RelocationId
group by r.OpenDate, r.CloseDate, e.GLAccountId, a.LastName, a.FirstName

Note that I used meaningful table aliases, and qualified all column names with the table they belong to. This greatly improves the readibility of the query, hence its maintainability.

Just a little thing, `ExpenseTypeId` is varchar and the contents don't match only numbers :) — Jorge Campos, Oct 26 '20 at 17:21

Need help getting SUM data from sql inner joins

1 Answers1