I have a query that provides a P&L view of my cost structure based on two tables with purchase data.
Select Left(A.Location, 5) as Site, A.Account, A.Category, A.Sub_Category,
sum(If(Month(Inv.Invoice_Date)=1, A.Cost, 0)) as Jan,
sum(If(Month(Inv.Invoice_Date)=2, A.Cost, 0)) as Feb,
sum(If(Month(Inv.Invoice_Date)=3, A.Cost, 0)) as Mar,
sum(If(Month(Inv.Invoice_Date)=4, A.Cost, 0)) as Apr,
sum(If(Month(Inv.Invoice_Date)=5, A.Cost, 0)) as May,
sum(If(Month(Inv.Invoice_Date)=6, A.Cost, 0)) as Jun,
sum(If(Month(Inv.Invoice_Date)=7, A.Cost, 0)) as Jul,
sum(If(Month(Inv.Invoice_Date)=8, A.Cost, 0)) as Aug,
sum(If(Month(Inv.Invoice_Date)=9, A.Cost, 0)) as Sep,
sum(If(Month(Inv.Invoice_Date)=10, A.Cost, 0)) as Oct,
sum(If(Month(Inv.Invoice_Date)=11, A.Cost, 0)) as Nov,
sum(If(Month(Inv.Invoice_Date)=12, A.Cost, 0)) as Dece,
sum(Inv.Cost) as Total,
From Table.A as A
Left join (Select Invoice_Number, Invoice_Line_Number, Invoice_Date, group by Invoice_Number, Invoice_Line_Number) as Inv
on Inv.Invoice_Number = A.Invoice_Number and Inv.Invoice_Line_Number = A.Invoice_Line_Number
Where Left(A.Location_Desc, 5) = 'TEX01'
and Year(Inv.Invoice_Date) = '2016'
Group by Site, Account, Category, Sub_Category
Having Total <> 0
;
So far so good. Now I need to do three things that I've been struggling with:
1) I need to have a Total Cost per month row at the bottom of every month column - is that possible? 2) I need to add variable rows that I can modify before running the query. For instance, If I find an investment opportunity to reduce cost by X amount, I would like to be able to introduce a row that subtracts that amount from the cost Total to show the savings. 3) I need to calculate the NPV of cost savings investment. This unfolds another problem. The query is only structured to bring 1 year worth of data, but NPV calculations require projections that span 5 to 10 years.
The optimal query would average out the cost of the Site and project the future cost with that average - then it would introduce the variable rows that reduce cost through new investments, and it would finally perform an NPV calculation to identify if the investment is worth pursuing.
My questions are:
a) Is MySQL the right tool for this? b) I know that from a simple select statement this is impossible - The view statement won't work here because I have a sub-query in the Select statement. Should I build a new table that holds the output of the query as data and use that table to perform all of this? c) Should I be looking into a Programming language/MySQL solution?
Thanks!!
For the First point you can get the result with below query
Select Left(A.Location, 5) as Site, A.Account, A.Category, A.Sub_Category,
sum(If(Month(Inv.Invoice_Date)=1, A.Cost, 0)) as Jan,
sum(If(Month(Inv.Invoice_Date)=2, A.Cost, 0)) as Feb,
sum(If(Month(Inv.Invoice_Date)=3, A.Cost, 0)) as Mar,
sum(If(Month(Inv.Invoice_Date)=4, A.Cost, 0)) as Apr,
sum(If(Month(Inv.Invoice_Date)=5, A.Cost, 0)) as May,
sum(If(Month(Inv.Invoice_Date)=6, A.Cost, 0)) as Jun,
sum(If(Month(Inv.Invoice_Date)=7, A.Cost, 0)) as Jul,
sum(If(Month(Inv.Invoice_Date)=8, A.Cost, 0)) as Aug,
sum(If(Month(Inv.Invoice_Date)=9, A.Cost, 0)) as Sep,
sum(If(Month(Inv.Invoice_Date)=10, A.Cost, 0)) as Oct,
sum(If(Month(Inv.Invoice_Date)=11, A.Cost, 0)) as Nov,
sum(If(Month(Inv.Invoice_Date)=12, A.Cost, 0)) as Dece,
sum(Inv.Cost) as Total,
From Table.A as A
Left join (Select Invoice_Number, Invoice_Line_Number, Invoice_Date, group by Invoice_Number, Invoice_Line_Number) as Inv
on Inv.Invoice_Number = A.Invoice_Number and Inv.Invoice_Line_Number = A.Invoice_Line_Number
Where Left(A.Location_Desc, 5) = 'TEX01'
and Year(Inv.Invoice_Date) = '2016'
Group by A.Site, A.Account, A.Category, A.Sub_Category with ROLLUP Having Total <> 0 AND
((a.Site is null or A.Account is not null) and (A.Account is null or A.Category is not null) and (A.Category is null or A.Sub_Category is not null));