Equivalent MYSQL output but using Google Sheets (script) or Microsoft Excel (macro) (must work with MacOS) to do the equivalent query command..
Existing MySQL structure:
Two tables (Activity & Allocations)
Activity - Fields:
credit_card
type
trans_date
post_date
description
amount
cost_center
Activity - Current Sample content:
"id","credit_card","type","trans_date","post_date","description","amount","cost_center"
67,"Chase","Sale","9/1/16","9/1/16","Transaction A",100.00,"N"
68,"Chase","Sale","9/2/16","9/2/16","Transaction B",200.00,"B"
85,"Chase","Sale","9/19/16","9/19/16","Transaction S",1900.00,"D"
86,"Chase","Sale","9/20/16","9/20/16","Transaction T",2000.00,"E"
87,"Chase","Sale","9/21/16","9/21/16","Transaction U",2100.00,"M"
88,"Chase","Sale","9/22/16","9/22/16","Transaction V",2200.00,"B"
89,"Chase","Sale","9/19/16","9/19/16","Transaction S",1900.00,"D"
90,"Chase","Sale","9/19/16","9/19/16","Transaction S",1900.00,"D"
91,"Chase","Sale","9/2/16","9/2/16","Transaction C",200.00,"B"
92,"Chase","Sale","9/2/16","9/2/16","Transaction D",200.00,"B"
Allocations - Fields:
cost_center
underlying_cost_company
allocation
Allocations - Current Sample content:
"id","cost_center","underlying_cost_company","allocation"
1,"A","Company A",1.0000
2,"B","Company B",1.0000
3,"C","Company C",1.0000
4,"D","Company D",1.0000
7,"E","Company E",1.0000
9,"F","Company F",1.0000
10,"G","Company G",1.0000
11,"H","Company H",1.0000
12,"I","Company I",1.0000
13,"J","Company J",1.0000
14,"K","Company K",1.0000
15,"L","Company L",1.0000
16,"M","Company M",0.5000
17,"M","Company N",0.5000
18,"N","Company O",0.2711
19,"N","Company Q",0.7289
Concept being that a cost center can have multiple underlying_cost_company so it is repeated until the sum is 1. (done manually)
Query being ran:
SELECT credit_card, `type`, trans_date, description, amount, Activity.cost_center, underlying_cost_company, allocation, round((amount * allocation),2) AS allocated_amount
FROM Activity
JOIN Allocations
ON Activity.cost_center=Allocations.cost_center
ORDER BY underlying_cost_company ASC
Query output:
"credit_card","type","trans_date","description","amount","cost_center","underlying_cost_company","allocation","allocated_amount"
"Chase","Sale","9/2/16","Transaction B",200.00,"B","Company B",1.0000,200.00
"Chase","Sale","9/22/16","Transaction V",2200.00,"B","Company B",1.0000,2200.00
"Chase","Sale","9/2/16","Transaction C",200.00,"B","Company B",1.0000,200.00
"Chase","Sale","9/2/16","Transaction D",200.00,"B","Company B",1.0000,200.00
"Chase","Sale","9/19/16","Transaction S",1900.00,"D","Company D",1.0000,1900.00
"Chase","Sale","9/19/16","Transaction S",1900.00,"D","Company D",1.0000,1900.00
"Chase","Sale","9/19/16","Transaction S",1900.00,"D","Company D",1.0000,1900.00
"Chase","Sale","9/20/16","Transaction T",2000.00,"E","Company E",1.0000,2000.00
"Chase","Sale","9/21/16","Transaction U",2100.00,"M","Company M",0.5000,1050.00
"Chase","Sale","9/21/16","Transaction U",2100.00,"M","Company N",0.5000,1050.00
"Chase","Sale","9/1/16","Transaction A",100.00,"N","Company O",0.2711,27.11
"Chase","Sale","9/1/16","Transaction A",100.00,"N","Company Q",0.7289,72.89
The above query, grabs all of the requeted data via a select command. Pairs it with its corresponding cost_center, then multiplies the amount by the allocation..
Overall objective to move the Excel or Google Sheets is to have each Table above be its own sheet and have the output of the query be its own sheet allowing the amount to have a $ included.. the allocation is just a decimal value.
It is in effect a pivot table of a pivot table but one that creates a new line per query the way it works in MySQL