CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Crezzer7
Crezzer7

Reputation: 2335

Get SUMs for dimension record from several fact tables

I have to 2 queries below which calculate to correct value:

Select Finances.CustomerID,
sum(Finances.Total)Total
from Finances
group by Finances.CustomerID
order by Finances.CustomerID

Select Invoices.CustomerID,
sum(Invoices.InvoiceValue)InvoiceValue
from Invoices
group by Invoices.CustomerID
order by Invoices.CustomerID

within the Finances table a customer can owe money from multiple orders, so for this example lets say the customer has to pay £100+ £400 + £500, which totals to £1000.

the customer then receives the 1st invoice for £100, and pays this..

so the 2nd query would now display £100.... from these 2 values I then want to calculate a 3rd column which deducts the £100 away from the £1000 equalling £900 (the amount left to pay)

the is my table structure:

enter image description here

there are a lot more tables associated however this is where I am the data from with the select statements :)

how would i write the sql statement to do this all together or is it not possible? ideally i would like the to be used within a INSERT -> SELECT -> WHERE NOT EXISTS Statement or alternatively populated in a view. thanks for the help

SAMPLE DATA: enter image description here

Upvotes: 1

Views: 147

Answers (2)

alj
alj

Reputation: 170

Try this,

CREATE VIEW CustomerFinanceDetails AS 
(

 select c.*,isnull(FinanceTotal,0) as FinanceTotal,isnull(InvoiceTotal,0) as InvoiceTotal 
 from 
 (select * from Customers) c
   left outer join 
 (select CustomerID,isnull(SUM(Total),0) as FinanceTotal from Finances group by CustomerID) f on c.CustumerID=f.CustomerID
   left outer join 
 (select CustomerID,isnull(SUM(InvoiceValue),0) as InvoiceTotal from Invoices group by CustomerID) i on c.CustumerID= i.CustomerID

)

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

CREATE VIEW CustomerFinances AS
    ;WITH FinancesCTE AS
    (
        Select Finances.CustomerID,
        sum(Finances.Total)Total
        from Finances
        group by Finances.CustomerID
    ),InvoicesCTE AS
    (
        Select Invoices.CustomerID,
        sum(Invoices.InvoiceValue)InvoiceValue
        from Invoices
        group by Invoices.CustomerID
    )
    SELECT C.*,
           F.Total AS FinanceTotal,
           I.InvoiceValue
    FROM   Customers C
           LEFT JOIN FinancesCTE F
               ON C.CustomerID = F.CustomerID
           LEFT JOIN InvoicesCTE I
               ON C.CustomerID = I.CustomerID

GO

Upvotes: 3

Related Questions