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

Blog

SQL Query Inner Join for SUM Amount

qfjr

Join Our Newsletter. No Spam, Only the good stuff.

Related