SQL SUM and divide linked tables

user_538820 0
Viewed 196

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 its 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 the Invoices table by the InvoiceID column. If there is 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 in Fiddle: http://sqlfiddle.com/#!18/9bd18/3

invoice group-by sum sql-server mysql
1 Answers

The data structure here isn't perfect, hence we need some extra code to solve for this. I needed to gather the amount of cars in each location, as well as to allocate the amounts for each invoice, depending on whether or not it was assigned to a location. I broke out the totals for each invoice type so that you can see the components which are being put together, you won't need those in your final result.

;WITH CarsByLocation AS(    
    SELECT  
         CostCenter
        ,COUNT(*) AS Cars
    FROM Cars 
    GROUP BY CostCenter
    UNION ALL
    SELECT  
         ''
        ,COUNT(*) AS Cars
    FROM Cars   
),CostCenterAssignedInvoices AS (
    SELECT 
         InvoicesCostCenterAllocations.CarLocation
        ,SUM(invoicevalue) / CarsByLocation.cars AS InvoiceTotal
    FROM Invoices 
    INNER JOIN InvoicesCostCenterAllocations ON invoices.InvoiceID = InvoicesCostCenterAllocations.InvoiceID
    INNER JOIN CarsByLocation on InvoicesCostCenterAllocations.CarLocation = CarsByLocation.CostCenter
    WHERE CostCenterAssigned = 1  --Not needed, put here for clarification
    GROUP BY InvoicesCostCenterAllocations.CarLocation,CarsByLocation.Cars
),UnassignedInvoices AS (
    SELECT 
         '' AS Carlocation
        ,SUM(invoicevalue)/CarsByLocation.Cars InvoiceTotal
    FROM Invoices 
    INNER JOIN CarsByLocation on CarsByLocation.CostCenter = ''
    WHERE CostCenterAssigned = 0
    group by CarsByLocation.Cars
)
SELECT 
      Cars.*
     ,cca.InvoiceTotal AS AssignedTotal
     ,ui.InvoiceTotal AS UnassignedTotal
     ,cca.InvoiceTotal + ui.InvoiceTotal AS Total
FROM Cars 
LEFT OUTER JOIN CostCenterAssignedInvoices CCA ON Cars.CostCenter = CCA.CarLocation
LEFT OUTER JOIN UnassignedInvoices UI ON UI.Carlocation = ''
ORDER BY 
     Cars.CostCenter
    ,Cars.PlateNo;
user_35938080
Related