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
Top comments (1)
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: