SQL SUM and divide linked tables

Question

Post reply

SQL SUM and divide linked tables

milo1981

Old Hand

Points: 375

May 28, 2021 at 3:49 pm

#3889276

I have the following tables:

create table Cars
(
CarID int,
CarType varchar(50),
PlateNo varchar(20),
CostCenter varchar(50),

);

insert into Cars (CarID, CarType, PlateNo, CostCenter) values 
(1,'Coupe','BC18341','CALIFORNIA'),
(2,'Hatchback','AU14974','DAKOTA'),
(3,'Hatchback','BC49207','NYC'),
(4,'SUV','AU10299','FLORIDA'),
(5,'Coupe','AU32703','NYC'),
(6,'Coupe','BC51719','CALIFORNIA'),
(7,'Hatchback','AU30325','IDAHO'),
(8,'SUV','BC52018','CALIFORNIA');

create table Invoices
(
InvoiceID int,
InvoiceDate date,
CostCenterAssigned bit,
InvoiceValue money 
);

insert into Invoices (InvoiceID, InvoiceDate, CostCenterAssigned, InvoiceValue) values 
(1, '2021-01-02', 0, 978.32),
(2, '2021-01-15', 1, 168.34),
(3, '2021-02-28', 0, 369.13),
(4, '2021-02-05', 0, 772.81),
(5, '2021-03-18', 1, 469.37),
(6, '2021-03-29', 0, 366.83),
(7, '2021-04-01', 0, 173.48),
(8, '2021-04-19', 1, 267.91);

create table InvoicesCostCenterAllocations
(
InvoiceID int,
CarLocation varchar(50)
);

insert into InvoicesCostCenterAllocations (InvoiceID, CarLocation) values 
(2, 'CALIFORNIA'),
(2, 'NYC'),
(5, 'FLORIDA'),
(5, 'NYC'),
(8, 'DAKOTA'),
(8, 'CALIFORNIA'),
(8, 'IDAHO');

How can I calculate the total invoice values allocated to that car based on it's cost center.

How can I calculate the total invoice values allocated to that car based on it's cost center.

If the invoice is allocated to cars in specific cost centers, then the CostCenterAssigned column is set to true and the cost centers are listed in the InvoicesCostCenterAllocations table linked to Invoices table by InvoiceID column. If there are no cost center allocation (CostCenterAssigned column is false) then the invoice value is divided by the total number of cars and summed up.

The sample data and my test query so far in Fiddle: http://sqlfiddle.com/#!18/9bd18/3

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

There doesn't appear to be any information in any of the tables that associates a given CarID with an invoice. The closest thing to that is the InvoicesCostCenterAllocations table but that's still not hitting the mark. You're missing the CarID/InvoiceID connection.

If you want to associate invoices to cars based on cost center, you may end up with more than 1 car per cost center. Is that what you want?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

milo1981 Old Hand Points: 375 More actions · Answer 2

The connection between Cars and Invoices is based on CostCenter column (this was CarLocation in Cars table, but I renamed it to CostCenter also to make more sense). I modified the script, please see: http://sqlfiddle.com/#!18/3c323b/5

I managed to calculate the invoice value per vehicle, but how can I display it in one query?

End result should be this:

PlateNumber | CostCenter | InvoiceValuePerVehicle

This reply was modified 3 years, 5 months ago by milo1981.

pietlinden SSC Guru Points: 63503 More actions · Answer 3

divide by the number of cars on an invoice?

Sounds like this would work most easily using AVERAGE windowing function. Just use the PARTITION BY part to do your grouping.

milo1981 Old Hand Points: 375 More actions · Answer 4

The cars are not directly associated with any invoice.

Some invoices (CostCenterAssigned=True) are associated with one ore more Cost Centers and some invoices (CostCenterAssigned=False) are not. The value of those that are not is divided by the total number of cars and the value of those that are associated with one ore more Cost Centers is divided by the number of cars in those specific Cost Centers.