SQL Query Inner Join for SUM Amount

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

Comments

[Jered Sanchez]

Hey hey. This may not be the cleanest solution but it will get what you're looking for. What I would do is create an outer aggregate query of a subquery of case statements for each Item.

Here's your query:

select
    OpenDate,
    CloseDate,
    Account,
    LastName,
    FirstName,
    SUM(Amount) as Amount,
    SUM(Item1) AS Item1,
    SUM(Item2) AS Item2,
    SUM(Item3) AS Item3,
    SUM(Item4) AS Item4,
    SUM(Item5) AS Item5,
    SUM(Item6) AS Item6,
    SUM(Item7) AS Item7,
    SUM(Item8) AS Item8

FROM (

    select 
        OpenDate,
        CloseDate,
        GLAccountId as Account,
        LastName,
        FirstName,
        t3.ExepnseTypeId,
        t3.Amount as Amount,
        CASE WHEN t3.ExepnseTypeId = 'Item 1' THEN t3.Amount END AS Item1,
        CASE WHEN t3.ExepnseTypeId = 'Item 2' THEN t3.Amount END AS Item2,
        CASE WHEN t3.ExepnseTypeId = 'Item 3' THEN t3.Amount END AS Item3,
        CASE WHEN t3.ExepnseTypeId = 'Item 4' THEN t3.Amount END AS Item4,
        CASE WHEN t3.ExepnseTypeId = 'Item 5' THEN t3.Amount END AS Item5,
        CASE WHEN t3.ExepnseTypeId = 'Item 6' THEN t3.Amount END AS Item6,
        CASE WHEN t3.ExepnseTypeId = 'Item 7' THEN t3.Amount END AS Item7,
        CASE WHEN t3.ExepnseTypeId = 'Item 8' THEN t3.Amount END AS Item8

    from dbo.Associates t1
    inner join dbo.Relocations t2
    on t1.AssociateId = t2.AssociateId
    inner join dbo.Expenses t3
    on t2.RelocationId = t3.RelocationId) AS [Data]

group by
    OpenDate,
    CloseDate,
    Account,
    LastName,
    FirstName

order by Account